Row removed based on criteria

MZING81 · Apr 4th 2012

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.

Code

Sub Removetextrow()[/FONT][/COLOR][COLOR=black][FONT=Verdana] [/FONT][/COLOR][COLOR=black][FONT=Verdana] Dim WS As Worksheet Application.ScreenUpdating = False    Application.Calculation = xlCalculationManual   For Each WS In ActiveWorkbook.Worksheets       With WSWith WS.UsedRange[/FONT][/COLOR][COLOR=black][FONT=Verdana]Do        Cells.Find(What:="Actual:", After:=ActiveCell, LookIn:=xlValues, LookAt _        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _        False, SearchFormat:=False).Activate    Selection.EntireRow.DeleteLoop[/FONT][/COLOR][COLOR=black][FONT=Verdana]   End With    Next WS    Application.ScreenUpdating = True    Application.Calculation = xlCalculationManualEnd Sub

[/FONT][/COLOR]
Any help will be greatly appreciated.

jproffer · Apr 4th 2012

Re: Row removed based on criteria

You're missing an End With after the other End With and before "Next WS"

MZING81 · Apr 4th 2012

Re: Row removed based on criteria

Quote from jproffer;603205

You're missing an End With after the other End With and before "Next WS"

Okay thanx that helped but now I just need to find out a way to get over the next error/ hurdle, which is run time error 91, object variable or with block variable not set

Code

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


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

Display More

jproffer · Apr 4th 2012

Re: Row removed based on criteria

Try this one:

Code

Sub Removetextrow()
     
    Dim WS As Worksheet
    Dim MyRange As Range
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For Each WS In ActiveWorkbook.Worksheets
        With WS
            With WS.Cells
                Do
                    Set MyRange = .Find("Actual:", LookIn:=xlValues, LookAt:=xlWhole)
                    If MyRange Is Nothing Then Exit Do
                    MyRange.EntireRow.Delete
                Loop
                     
                End With
            End With
        Next WS
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationManual
    End Sub

Display More

Row removed based on criteria

Participate now!

Share