Deleting a Row if it contains a specific word

S

Steve Madsen

I want to develop a macro that will search through a worksheet and delete any
row(s) that contains the word "Step". The word could occur anywhere within
any cell in the row.

Thanks for any help offered.
 
R

Ron de Bruin

Hi Steve

Look here for a few ways
http://www.rondebruin.nl/delete.htm

Try

Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

If Application.WorksheetFunction.CountIf(.Rows(Lrow), "Step") > 0 Then
.Rows(Lrow).Delete
End If

Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub
 
Top