Removing lines of data that do not contain required data (macro)

[Sean]

I would like to delete rows of data that do NOT contain
the word "Total" in column A.

An earlier response from Rob suggested the following would
work:

Sub test()
Dim rng As Range, i As Long

With ActiveSheet
For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
If LCase(.Cells(i, 1).Value) Like "*total*"
Then
If rng Is Nothing Then Set rng = Rows(i)
Else Set rng =
Union(rng, Rows(i))
End If
Next
If Not rng Is Nothing Then rng.Delete xlShiftUp
End With
End Sub


however, this Deletes the rows that contain the
word "Total" and keeps the other non "Total" rows (which
is the opposite of what I want.

Any ideas ?

 

[Frank Kabel]

Hi
replace the line
If LCase(.Cells(i, 1).Value) Like "*total*" Then

with the following statement:
If InStr(LCase(.Cells(i, 1).Value),"total")=0 Then