Remove row based on text Criteria

**MZing81** · 04-04-2012, 01:37 PM

I have this code that I wrote to remove all rows across all sheets that have the text, actual:, in it but doesn't work. Keep getting the error Next without for, but not sure where exactly to edit it.


Sub Removetextrow()
 
 Dim WS As Worksheet
 Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
   For Each WS In ActiveWorkbook.Worksheets
       With WS
With WS.UsedRange
Do
        
Cells.Find(What:="Actual:", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Selection.EntireRow.Delete
Loop

   End With
    Next WS
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationManual
End Sub

Any help will be greatly appreciated.

**Paul** · 04-04-2012, 01:46 PM

Hi MZing,

It looks like you had "With WS" and "With WS.UsedRange", but only one "End With". Try this instead:

Sub Removetextrow()
Dim WS As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
   
For Each WS In ActiveWorkbook.Worksheets
    With WS.UsedRange
        Do
            Cells.Find(What:="Actual:", After:=ActiveCell, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
            Selection.EntireRow.Delete
        Loop
   End With
Next WS

Application.ScreenUpdating = True
Application.Calculation = xlCalculationManual
End Sub