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

Question

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 = thisTable.Rows.Count To 1 Step -1
    If WorksheetFunction.CountA(thisTable.Rows(thisRow)) = 0 Then
        thisTable.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 = thisTable.Rows.Count To thisTable.DataBodyRange.Rows.Count Step -1
    If WorksheetFunction.CountA(thisTable.Rows(thisRow)) = 0 Then
        thisTable.Rows(thisRow).EntireRow.Delete
    End If
Next thisRow

Any ideas?

EDIT

Replaced vbNullString by 0 since CountA returns a numerical value (and not a string, which would be needed to use the vbNullString property).
The purpose of these code samples is to remove blank rows in a given table.
Replaced For thisRow = Selection.Rows.Count with For thisRow = thisTable.Rows.Count as the Selection was part of another script and did not make sense here.

What do you think the latter will do? It doesn't make a lot of sense to me. Also, `CountA` returns a number, not a string. — Rory, Mar 30 '22 at 11:25
The first one will loop through every row in the worksheet...the second will only loop through the unused rows...so depending on the size of the used section, could be a ton of rows saved. — Rdster, Mar 30 '22 at 11:29
What makes your code highly unusual is that you are using `Selection.Rows.Count` but it is unclear what `Selection` actually is. I think it would be better if you'd explain what these codes should be doing and where the difference between them is supposed to be. It is clear that you want to delete some rows but when you do this to a table, it would be correct to just delete the **table rows**, not the **entire worksheet rows**. Please do clarify. BTW, `thisTable.DataBodyRange.Rows.Count` is the last (data) row number of the table, while `thisTable.DataBodyRange.Row` is the first data row. — VBasic2008, Mar 30 '22 at 11:43
@freeflow MS help page shares information about default `STEP` value but not the `END` one: https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/for-next-statement — James69, Mar 31 '22 at 16:48
@freeflow same thing here: [link](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/fornext-statement) and here: [link](https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/using-fornext-statements) — James69, Apr 04 '22 at 09:07
@VBasic2008 I've made some changes in the original code (c.f. EDIT) to clarify the objective behind the scripts. In this case, the goal is to delete blank rows in a given table. — James69, Apr 04 '22 at 09:18

Gove · Answer 1 · 2022-03-30T14:57:01.323

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.

@James69 - just curious, which part of this description was helpful? — Gove, Mar 30 '22 at 14:45
This: **_When attempting the next iteration of the loop, the interpreter notices that X is out of bounds and exits the loop_**, This : **_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._** And that : **_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._** — James69, Apr 05 '22 at 08:30
And then it's a question of understanding why the X is considered being out of bounds when moving the value to 0. In Python for instance, **[-1]** means last element: [what does -1 mean in python - slicing?](https://stackoverflow.com/questions/44133446/what-does-1-mean-in-python-slicing/44133585#44133585). It's a general rule although I haven't found any concrete explanation, yet. — James69, Apr 05 '22 at 08:36

VBasic2008 · Answer 2 · 2022-04-06T15:58:34.163

Delete Blank (or Empty) Excel Table Rows

Argumented

Sub DeleteBlankTableRowsTest()

    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Data")
    Dim tbl As ListObject: Set tbl = ws.ListObjects(1)
    
    DeleteBlankTableRows tbl

End Sub

Sub DeleteBlankTableRows(ByVal tbl As ListObject)

    If tbl Is Nothing Then Exit Sub
    If tbl.DataBodyRange Is Nothing Then Exit Sub
    
    Dim cCount As Long: cCount = tbl.DataBodyRange.Columns.Count
    
    Dim drg As Range ' Delete Range
    Dim rrg As Range ' (Table) Row Range
    
    For Each rrg In tbl.DataBodyRange.Rows
        If Application.CountBlank(rrg) = cCount Then
            If drg Is Nothing Then
                Set drg = rrg
            Else
                Set drg = Union(drg, rrg)
            End If
        End If
    Next rrg
    
    If Not drg Is Nothing Then drg.Delete xlShiftUp

End Sub

Compact

Sub DeleteBlankTableRowsCompact()

    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Data")
    Dim tbl As ListObject: Set tbl = ws.ListObjects(1)
    If tbl.DataBodyRange Is Nothing Then Exit Sub
    
    Dim cCount As Long: cCount = tbl.DataBodyRange.Columns.Count
    
    Dim drg As Range ' Delete Range
    Dim rrg As Range ' (Table) Row Range
    
    For Each rrg In tbl.DataBodyRange.Rows
        If Application.CountBlank(rrg) = cCount Then
            If drg Is Nothing Then
                Set drg = rrg
            Else
                Set drg = Union(drg, rrg)
            End If
        End If
    Next rrg
    
    If Not drg Is Nothing Then drg.Delete xlShiftUp

End Sub

Reflecting Empty

Blank = Empty or ="" or '.
If you want to delete empty rows i.e. you want to keep rows that are blank but not empty i.e. rows that contain cells with ="" or ', in the codes replace...
```
If Application.CountBlank(rrg) = cCount Then
```
... with...
```
If Application.CountA(rrg) = 0 Then
```
... and remove the lines Dim cCount As Long: cCount = tbl.DataBodyRange.Columns.Count and rename the procedures appropriately.

I tried your compact version and it deletes all rows in the given table. And it doesn't do anything when replacing `If Application.CountBlank(rrg) > 0 Then` with `If Application.CountA(rrg) = 0 Then` — James69, Apr 06 '22 at 15:42

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

EDIT

2 Answers2

Delete Blank (or Empty) Excel Table Rows