VBA: Deleting Rows with a Specific Value

delete-row, excel, vba

Solution

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

Problem

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?

Original source