Best way to specify the end of a For... Next loop-CodePudding

This is the general For...Next loop statement:

For i = START To END [step]
     'Repeated action
Next i

I'm trying to understand the difference between writing "1" as the END of a For...Next statement and using the total number of rows in a given table.

In other words, the difference between this code sample:

Dim thisSheet As Worksheet
Dim thisTable As ListObject
Dim thisRow As Long

Set thisSheet = ThisWorkbook.Worksheets("Data")
Set thisTable = thisSheet.ListObjects(1)

For thisRow = Selection.Rows.Count To 1 Step -1
    If WorksheetFunction.CountA(Selection.Rows(thisRow)) = vbNullString Then
        Selection.Rows(thisRow).EntireRow.Delete
    End If
Next thisRow

And this one:

Dim thisSheet As Worksheet
Dim thisTable As ListObject
Dim thisRow As Long

Set thisSheet = ThisWorkbook.Worksheets("Data")
Set thisTable = thisSheet.ListObjects(1)

For thisRow = Selection.Rows.Count To thisTable.DataBodyRange.Rows.Count Step -1
    If WorksheetFunction.CountA(Selection.Rows(thisRow)) = vbNullString Then
        Selection.Rows(thisRow).EntireRow.Delete
    End If
Next thisRow

Any ideas?

CodePudding user response：

Before trying to help you understand how the start and end values for a "For...Next" loop operate, there appears to be a problem with the code in the loop that will prevent it from doing what you want regardless of how many times the loop iterates. In your IF statement, the following condition can never be true:

WorksheetFunction.CountA(Selection.Rows(thisRow)) = vbNullString

The worksheet function "CountA" will always return a number and thus it cannot equal a null string. So I don't see how either example you provide can ever delete a row.

OK. On to the exit conditions of the For..Next loop...

The general use of a For...Next loop is to iterate a specified number of times while moving the value of a variable across a specified set of integers. Consider this loop:

For X = 1 to 10
    Debug.Print X
Next

Here, the statement "Debug.print X" will be executed 10 times. The first time, it will print "1" to the immediate windows, the second time it will print "2" and so forth until it prints "10". After printing 10, the interpreter will reach the "Next" clause, changing X to 11. When attempting the next iteration of the loop, the interpreter notices that X is out of bounds and exits the loop

In your example, you are using the "Step" clause to add -1 to the control variable each time the interpreter processes "Next". Consider this example.

For X = 10 to 1 Step -1
    Debug.Print X
Next

Here, the initial value of X is 10, sot the first time the interpreter processes "Debug.print X" it will print 10. When reaching "Next", the interpreter will add -1 to X, so the next time through the loop, the interpreter will print "9", then "8" and so on until it prints "1". Then the "Next" statement will move the value of X to 0 so when trying run the next iteration of the loop, the interpreter notices that X is now out of the bounds set and it exits the loop.

An important point is that the "For...Next" loop only evaluates the expression that determines the boundary of the control variable (X) in my example when it first enters the loop. Whatever the expression evaluates to at that point is the value that determines the exit condition.

In your first example, the VBA Interpreter will evaluate "Selection.Rows.Count" to an integer value determined by the number of rows that are in the current selection on whichever sheet is active. This will be the value of "thisRow" the first time through the loop. Each time the interpreter gets to "Next" it will add -1 to thisRow until this row is less than 1, which will cause the interpreter to exit the loop.

In your second example, in addition to evaluating "Selection.Rows.Count" to determine the first value of thisRow, the interpreter also evaluates "thisTable.DataBodyRange.Rows.Count" to determine what is the ending value for this row. That expression will evaluate to an integer based on how many rows are in the data table referenced by the variable named "thisTable". That value will determine the exit condition for the loop even if the value of "thisTable.DataBodyRange.Rows.Count" changes as a result of the code executing in the loop. It is only the expression's initial value that matters in deciding when the loop ends.