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

I can't tell what's wrong!

Answer 1

I know you have already got what you were looking for. However, still here is another method using Autofilter. This is much faster than looping through each row and checking for the value.

Sub Sample()
    Dim lastRow As Long

    With Sheets("Sheet1")

        lastRow = .Range("A" & Rows.Count).End(xlUp).Row

        '~~> Remove any filters
        .AutoFilterMode = False

        '~~> Filter, offset(to exclude headers) and delete visible rows
        With .Range("A1:A" & lastRow)
            .AutoFilter Field:=1, Criteria1:="TRUE"
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With
End Sub

HTH

Answer 2

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