Each row in range skips every second value

Question

I want to loop over all rows in the active worksheet and compare the values of a column.

However, it skips every second value.

The worksheet looks like this:

Code:

Sub test()

    Dim ws As Worksheet
    Dim r As Range
    Dim total As Integer
    Dim value As String

    Set ws = ActiveSheet
    total = 0

    For Each r In ws.UsedRange.rows

        value = r.Cells(r.Row, 3).value

        If value = "Test" Then

            'Do something...
            total = total + 1

        End If

       Debug.Print ("Row: " & r.Row & " - Value: " & value)

    Next r

End Sub

The console shows this:

Row: 1 - Value: Col2
Row: 2 - Value: JohnDoe
Row: 3 - Value: JohnDoe
Row: 4 - Value: Test
Row: 5 - Value: Blabla
Row: 6 - Value: Blabla
Row: 7 - Value: 
Row: 8 - Value: 
Row: 9 - Value: 
Row: 10 - Value: 
Row: 11 - Value:

How do I fix this, so it does not skip every second value?

The reason I am using UsedRange instead of for i = 1 to x, is that I will not now the number of rows beforehand.

Don't use `UsedRange`... [Find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, May 29 '20 at 14:23
@BigBen ws.UsedRange.rows in this case give the correct number of rows, so I don't think it will change anything. — 2by, May 29 '20 at 14:27
Please re-read the answer to that question and *don't* use `UsedRange`... — BigBen, May 29 '20 at 14:27
@JvdV my goal is to test if the value of column C is "Test" and then execute a number of commands. I simplified the example here — 2by, May 29 '20 at 14:28
Then use [`FindNext`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.findnext) to only loop over cells that hold `Test`. Looping all cells is redundant. =) — JvdV, May 29 '20 at 14:29

score 0 · Accepted Answer · answered May 29 '20 at 14:35

Suggestions:

Avoid using UsedRange: see this question why.
Avoid looping over the Rows if you don't need to... also avoid referencing r.Cells as this will not be an absolute reference (i.e. relative to the whole sheet), but a relative reference based on r.

With ws
    Dim lastRow as Long
    lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

    For i = 2 to lastRow
       If .Cells(i, "C").Value = "Test" Then
           ' do your stuff, though this loop is still inefficient
       End If
    Next
End With

score 0 · Answer 2 · answered May 29 '20 at 14:37

The solution, thanks to @BigBen

Sub test_solution()

    'Variables
    Dim ws As Worksheet
    Dim r As Range
    Dim total As Integer
    Dim value As String
    Dim lastRow
    Dim i As Integer
    Set ws = ActiveSheet
    total = 0

    With ws
        lastRow = Range("C" & .rows.Count).End(xlUp).Row
    End With

    For i = 1 To lastRow

        value = Cells(i, 3).value

        If value = "Test" Then

            'Do something

            total = total + 1

        End If

       Debug.Print ("Row: " & i & " - Value: " & value)

    Next i

End Sub

Gives correct output:

Row: 1 - Value: Col2
Row: 2 - Value: Test
Row: 3 - Value: JohnDoe
Row: 4 - Value: Test
Row: 5 - Value: JohnDoe
Row: 6 - Value: JohnDoe
Row: 7 - Value: Test
Row: 8 - Value: Blabla
Row: 9 - Value: Blabla
Row: 10 - Value: Test
Row: 11 - Value: Blabla

Each row in range skips every second value

2 Answers2