Deleting lines in a macro

S

Sean

I am completely new to macros so please accept my
apologies if what i am asking is very basic.

I have a spreadsheet full of data (sheet1). I want to
delete all rows in the spreadsheet that do not contain the
word "Total" in column A.

I want to know how to do this if:
i) Any part of the cell contains the word "Total"
ii) The cell only contains the word "Total" (and nothing
else)


Thanks
 
R

Rob van Gelder

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
 
Top