CODEWITHSUNDEEP
excelvba
JesseE
JesseE

Reputation: 33

Macro: Delete row if value does not match this or that

I am trying to modify the following code that if it does not match "X" or "Y" then delete the entire row. I tried to add just '"Or "Y"' but I get a type mismatch

For Lrow = Lastrow To Firstrow + 1 Step -1
     With .Cells(Lrow, "AY")
        If Not IsError(.Value) Then
            If Not .Value Like "X" Then .EntireRow.Delete
        End If
    End With
Next Lrow

Any thoughts? Or suggestions for replacement?

Upvotes: 0

Views: 2038

Answers (2)

Xabier
Xabier

Reputation: 7735

How about the code below::

For Lrow = Lastrow To Firstrow + 1 Step -1
     With .Cells(Lrow, "AY")
        If Not IsError(.Value) Then
            If .Value <> "X" AND .Value <> "Y" Then .EntireRow.Delete
        End If
    End With
Next Lrow

Upvotes: 0

CLR
CLR

Reputation: 12279

Be very careful using ANDs and ORs around Not - you have to think it through:

NOTE. NOT(aORb) is the same as NOT(a)ANDNOT(b)

The following code will do what you need:

For Lrow = Lastrow To Firstrow + 1 Step -1
    With ActiveSheet.Cells(Lrow, "AY")
        If Not (.Text = "X" Or .Text = "Y") Then .EntireRow.Delete
    End With
Next Lrow

The following line would also work instead:

       If Not (.Text = "X") And Not(.Text = "Y") Then .EntireRow.Delete

Upvotes: 1

Related Questions