Loop through each row of a range in Excel
Dim a As Range, b As RangeSet a = SelectionFor Each b In a.Rows MsgBox b.AddressNext
Something like this:
Dim rng As RangeDim row As RangeDim cell As RangeSet rng = Range("A1:C2")For Each row In rng.Rows For Each cell in row.Cells 'Do Something Next cellNext row
Just stumbled upon this and thought I would suggest my solution. I typically like to use the built in functionality of assigning a range to an multi-dim array (I guess it's also the JS Programmer in me).
I frequently write code like this:
Sub arrayBuilder()myarray = Range("A1:D4")'unlike most VBA Arrays, this array doesn't need to be declared and will be automatically dimensionedFor i = 1 To UBound(myarray) For j = 1 To UBound(myarray, 2) Debug.Print (myarray(i, j)) Next jNext iEnd Sub
Assigning ranges to variables is a very powerful way to manipulate data in VBA.