Microsoft Office Tutorials and References
In Depth Information
Looping In Reverse with Step
If wb.Name = “Test.xlsx” Then
wb.Save
wb.Close
‘Exit For statement to avoid needless iterations if the condition is met.
Exit For
End If
‘Loop to the next iteration.
Next wb
End Sub
looping in reverse with step
A common request that Excel users have is to insert an empty row when the value of a cell in some
particular column does not equal the value of the cell below it. In Figure 9-1, the table of data is
sorted by Region in column A, and the request is to visually separate the regions with an empty row
at each change in Region name.
When inserting a series of rows like
this, it’s best to start looping from the
bottom of the table, and work your way
up to the top. That means your numeric
row reference in the loop will be
decreasing and not increasing, because
your starting point is row 18 (the last
row of data) and your ending point is
row 2 (the first row of data).
Recall that when VBA executes a For…
Next loop, by default it increments by
1 the value of your declared Integer
or Long type variable. With For…Next
loops, you can specify an alternative
increment or decrement value by using
the optional Step keyword. You can step
forward or backward by as large a numeric value as you like.
figurE 9-1
In this example, each cell in column A is being evaluated one by one, from row 18 to row 2, so the
loop will step by a numeric factor of negative 1. Here is a macro that makes the “Before” image look
like the “After” image in Figure 9-1:
Sub InsertRows()
‘Declare your Integer or Long variable.
Dim xRow As Long
‘Open a For Each loop.
For xRow = 18 To 3 Step -1
‘Command(s) to be executed.
If Range(“A” & xRow).Value <> Range(“A” & xRow - 1) Then
Rows(xRow).Resize(1).Insert
End If
‘Loop to the next iteration.
Next xRow
End Sub
Search JabSto ::




Custom Search