VBA: Deleting Rows with a Specific Value

Question

I'm trying to write a macro to delete all rows that have True in column A.

Here is what I have so far:

Sub deleteBlankRows3()
    Dim lastrow as Long
    Dim x as Long

    lastrow = 4650
    For x=8 to x=lastrow
        If (Range("A1").Offset(x,0) = True) Then
            Range("A1").Offset(x,0).EntireRow.Delete
            x = x + 1
        End if
    Next x

End Sub

How can I make the above code work?

Answer 1

Three things might be at work here.

First, you should be looking at the cell's value explicitly if you're testing for equivalence for the underlying value:

If (Range("A1").Offset(x,0).Value = True) Then

Without saying.Value, I think the cell by default returns it's Text property for equivalence tests against a non-range property.

Second, your cells probably contain a string "True", rather than the value True so try using

If (Range("A1").Offset(x,0).Value = "True") Then

Finally, if you actually find a row, and you delete it, then your will actually end up skipping a row, because all of the rows after the row being deleted will shift down (row 5 becomes row 4, etc), but you also just incremented x, so you will be skipping the row right after every row you deleted. To fix this, either loop in decreasing order:

For x=lastrow to 8 step -1

or don't increment x if you've just deleted a row:

If (Range("A1").Offset(x,0).Value = "True") Then
    Range("A1").Offset(x,0).EntireRow.Delete
Else
    x = x + 1
EndIf