在Excel中循环遍历范围内的每一行

Question

这是一件我确定有内置函数可以实现的事情（而且我可能以前已经被告知了），但我现在想不起来了。

如何使用Excel VBA循环遍历多列范围中的每一行？我搜寻到的所有教程似乎只提到如何处理一维范围...

- Margaret

---

http://codevba.com/excel/for_each_cell_in_range.htm#.Xyd8qCgzaUk - Frank Myat Thu

Answer 1

Dim a As Range, b As Range

Set a = Selection

For Each b In a.Rows
    MsgBox b.Address
Next

- Mike

Answer 2

类似这样：

Dim rng As Range
Dim row As Range
Dim cell As Range

Set rng = Range("A1:C2")

For Each row In rng.Rows
  For Each cell in row.Cells
    'Do Something
  Next cell
Next row

- David Andres

---

我尝试了一下，row.Columns 可用——但 row.Cells 不行。 - Dan

Answer 3

刚刚偶然发现这个问题，想给出我的解决方案。通常我喜欢使用赋值数组范围的内置功能（我猜这也是我的 JS 程序员本性）。

我经常写这样的代码：

Sub arrayBuilder()

myarray = Range("A1:D4")

'unlike most VBA Arrays, this array doesn't need to be declared and will be automatically dimensioned

For i = 1 To UBound(myarray)

    For j = 1 To UBound(myarray, 2)

    Debug.Print (myarray(i, j))

    Next j

Next i

End Sub

将范围分配给变量是在VBA中操纵数据的一种非常强大的方式。

- tc_NYC

---

使用数组方法有两个主要优点：1）数组方法始终比循环范围更快，2）它简单易用，可以在两个方向上使用，并在一些计算后将数组写回：Range("A1:D4") = myarray。请注意：Dim myarray 为变体；请注意默认情况下它是一个基于1的二维数组。 - T.M.

Answer 4

在循环中，我总是更喜欢使用Cells类，使用R1C1参考方法，就像这样：

Cells(rr, col).Formula = ...

这使我能够快速、轻松地循环遍历单元格范围：

Dim r As Long
Dim c As Long

c = GetTargetColumn() ' Or you could just set this manually, like: c = 1

With Sheet1 ' <-- You should always qualify a range with a sheet!

    For r = 1 To 10 ' Or 1 To (Ubound(MyListOfStuff) + 1)

        ' Here we're looping over all the cells in rows 1 to 10, in Column "c"
        .Cells(r, c).Value = MyListOfStuff(r)

        '---- or ----

        '...to easily copy from one place to another (even with an offset of rows and columns)
        .Cells(r, c).Value = Sheet2.Cells(r + 3, 17).Value


    Next r

End With

- LimaNightHawk