使用数组 VBA Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18316714/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Working with Arrays VBA Excel
提问by Richard Pullman
I am developing a macro to eliminate blank rows from a worksheet which is used for entering customized orders. Lets say rows 7,8,9 and 12 have contents. I want to move the contents of row 12 to row 10.
我正在开发一个宏来消除用于输入自定义订单的工作表中的空白行。假设第 7、8、9 和 12 行有内容。我想将第 12 行的内容移动到第 10 行。
So far I've located the last occupied row in column c then identified whether the cell in the row in column e is blank or not.
到目前为止，我已经找到了 c 列中最后一个被占用的行，然后确定了 e 列中行中的单元格是否为空。
Now I want to put a value into an array either 0 (blank) or 1 (occupied). I am getting an error (object required) on the line of code that sets the value of stones (1) to 1 or 0.
现在我想将一个值放入一个数组中，要么是 0（空白）要么是 1（被占用）。我在将石头 (1) 的值设置为 1 或 0 的代码行上收到错误（需要对象）。
What is going wrong?
出了什么问题？
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("Belmont")
Set rng1 = ws.Columns("c").Find("*", ws.[c1], xlValues, , xlByRows, xlPrevious)
Dim zen As String
zen = rng1.Address(0, 0)
Range(zen).Select
Set ruby = Window.ActiveCell.Row - 11
Dim stones() As Boolean
ReDim stones(1 To ruby)
If IsEmpty(ActiveCell.Offset(2, 0)) Then
Set stones(1) = 0
Else
Set stones(1) = 1
End If
msg55 = MsgBox(stones(1), vbDefaultButton1, "Gekko")
回答by Stepan1010
My assumption is that you are doing this for purposes of learning rather than practicality:
我的假设是你这样做是为了学习而不是实用性：
You could google VBA arrays and get a plethora of material on the subject. I would start here: http://www.cpearson.com/excel/vbaarrays.htm
您可以谷歌 VBA 数组并获得有关该主题的大量材料。我会从这里开始：http: //www.cpearson.com/excel/vbaarrays.htm
You would declare your array like so:
你会像这样声明你的数组：
Dim stones(1 To 10) As Double
You're going to have to iterate through each cell in your range. You can Google how to do that as well:
您将不得不遍历范围内的每个单元格。你也可以谷歌如何做到这一点：
Loop through each cell in a range of cells when given a Range object
当给定 Range 对象时，循环遍历单元格范围内的每个单元格
You can set the value of the 5th element in the array to the value of 10 like so:
您可以将数组中第 5 个元素的值设置为 10，如下所示：
stones(5) = 10
It really seems like you need to do some basic VBA programming tutorials. You could start here:
看起来您确实需要学习一些基本的 VBA 编程教程。你可以从这里开始：
回答by tigeravatar
If you're trying to get rid of blank cells in sheet 'Belmont' column C, then this should work for you:
如果您试图摆脱工作表“贝尔蒙特”C 列中的空白单元格，那么这应该适合您：
Sub tgr()
Dim rngBlanks As Range
With Sheets("Belmont").Range("C1", Sheets("Belmont").Cells(Rows.Count, "C").End(xlUp))
On Error Resume Next
Set rngBlanks = .SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rngBlanks Is Nothing Then rngBlanks.EntireRow.Delete
End With
Set rngBlanks = Nothing
End Sub
回答by Gary's Student
If you want to delete all rows in which column Cis blank, then:
如果要删除C列为空的所有行，则：
Sub dural()
Dim r As Range
Set r = Range("C:C").Cells.SpecialCells(xlCellTypeBlanks).EntireRow
r.Delete
End Sub
will accomplish this without looping.
无需循环即可完成此操作。
