Conditional formatting VBA for not equal to

[timot18]

Hi, I am trying to delete rows in a list if one of the cells does not equal certain text. I think I am being stupid. At present I am using the code:

Range("d65536").End(xlUp).Select
While ActiveCell.Row > 1
If ActiveCell Like "*=*" Then ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select

Wend

But I want the row to be deleted if the Active cell DOES NOT have certain text in it. Can someone quickly help me.

Thanks

 

[P Sitaram]

If Instr(1,ActiveCell,"MyText") > 0 Then ActiveCell.EntireRow.Delete

 

[timot18]

Hi I have put in the following code:

Sub Delete_Search()

Range("d65536").End(xlUp).Select
While ActiveCell.Row > 1
If InStr(1, ActiveCell, "*=*") > 0 Then ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select

Wend

I want the macro to delete rows where cells in column D to not have an "=" sign. And unfortunately this does not work. Any ideas?

Thanks

 

[P Sitaram]

1. The *s around = are not needed.

2. If the `=' is part of a text entry in the cell, this will work.

3. If your checking whether the cell has a formula, this will not work. Instead try If ActiveCell.HasFormula

 

[timot18]

The code that was given deletes all rows where the value in active cell is present. I am hoping to find code that deletes rows where the value in the active cell is NOT present.

Sorry for any confusion

Tim. :wink:

 

[P Sitaram]

My mistake. change the line to:

If InStr(1, ActiveCell, "*=*") = 0 Then ActiveCell.EntireRow.Delete

 

[timot18]

Thanks thats great. I know its cheeky but can you tell me how to stop the loop when it hits line 4 i have been using the following but it doesnt seem to work:

Sub Delete_Search()

Range("d65536:d4").End(xlUp).Select
While ActiveCell.Row > 1
If InStr(1, ActiveCell, "=") = 0 Then ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select

Wend

End Sub

Thanks

 

[P Sitaram]

Try:

Dim LastRow as Long
LastRow = [D65536].End(xlUp).Row
Do while LastRow > 3
     With Range("D" & LastRow)
            If InStr(1, .Value, "=") = 0 Then .EntireRow.Delete
      End With
      LastRow = LastRow -1
Loop

 

[timot18]

Thanks for your help that worked great!