VBA Delete all rows below if...

[george hart]

I need to delete all rows in a worksheet and the one/s including the name "Other Vehicles" or "Class 57". The problem I have is using something like the below doesn't help when theres caps/blanks cells between text???

In short, find "Other Vehicles" or "Class 57" and delete everything below it.


Cells.Find(What:="Other Vehicles", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Rows("24:55").Select
Selection.Delete Shift:=xlUp
Rows("23:23").Select
Selection.Delete Shift:=xlUp

 

[Sektor]

Where do you search for values? Entire worksheet?

 

[george hart]

Hi

Thanks for your response...

I need to check these worksheets if that helps:

Tabs = Array("LAIRA STOP", "LANDORE", "SPM", "OOC STOP")

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] DeleteAllRows()

    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] arrSheets [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR], cell [COLOR="Blue"]As[/COLOR] Range
    
    arrSheets = VBA.Array("LAIRA STOP", "LANDORE", "SPM", "OOC STOP")
    
    [COLOR="Blue"]For[/COLOR] i = 0 [COLOR="Blue"]To[/COLOR] [COLOR="Blue"]UBound[/COLOR](arrSheets)
        [COLOR="Blue"]With[/COLOR] Worksheets(arrSheets(i))
            [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] cell [COLOR="Blue"]In[/COLOR] .UsedRange
                [COLOR="Blue"]If[/COLOR] cell.Value [COLOR="Blue"]Like[/COLOR] "*" & "Other Vehicles" & "*" [COLOR="Blue"]Or[/COLOR] _
                   cell.Value [COLOR="Blue"]Like[/COLOR] "*" & "Class 57" & "*" [COLOR="Blue"]Then[/COLOR]
                   cell.EntireRow.Delete
                [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
            [COLOR="Blue"]Next[/COLOR]
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    [COLOR="Blue"]Next[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[george hart]

Many thanks

Your code works in that it finds and deletes the row if cells contains "Other Vehicles" or "Class 57", but it doesn't delete all the remaining rows below?? Bearing in mind, the cells/rows below may contain anything.

What I need is Find "Other Vehicles" or "Class 57" and delete that row and all the ones below regardless of what they contain.

Many thanks again for your with this...

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] DeleteAllRows()

    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] arrSheets [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR], cell [COLOR="Blue"]As[/COLOR] Range
    
    arrSheets = VBA.Array("LAIRA STOP", "LANDORE", "SPM", "OOC STOP")
    
    [COLOR="Blue"]For[/COLOR] i = 0 [COLOR="Blue"]To[/COLOR] [COLOR="Blue"]UBound[/COLOR](arrSheets)
        [COLOR="Blue"]With[/COLOR] Worksheets(arrSheets(i))
            [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] cell [COLOR="Blue"]In[/COLOR] .UsedRange
                [COLOR="Blue"]If[/COLOR] cell.Value [COLOR="Blue"]Like[/COLOR] "*" & "Other Vehicles" & "*" [COLOR="Blue"]Or[/COLOR] _
                   cell.Value [COLOR="Blue"]Like[/COLOR] "*" & "Class 57" & "*" [COLOR="Blue"]Then[/COLOR]
                   .Range(.Cells(cell.Row, 1), .Cells(Rows.Count, 1)).EntireRow.Delete
                [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
            [COLOR="Blue"]Next[/COLOR]
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    [COLOR="Blue"]Next[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[george hart]

Thats great...works a treat.

Thanks very much. I was pulling my hair out last night trying to sort this...

 

[Sektor]

Not at all! Glad that helped you!

 

[george hart]

Hi again

Just one question. What code would I need should the tabs in question not exist.

For example if one was to delete on the tabs (or all of them) the code would fall over. I'd like it to just stop and issue a messege or something??

Any ideas??

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] DeleteAllRows()

    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] arrSheets [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR], cell [COLOR="Blue"]As[/COLOR] Range
    
    arrSheets = VBA.Array("LAIRA STOP", "LANDORE", "SPM", "OOC STOP")
    
    [COLOR="Blue"]For[/COLOR] i = 0 [COLOR="Blue"]To[/COLOR] [COLOR="Blue"]UBound[/COLOR](arrSheets)
        [COLOR="Blue"]On[/COLOR] [COLOR="Blue"]Error[/COLOR] [COLOR="Blue"]Resume[/COLOR] [COLOR="Blue"]Next[/COLOR]
        [COLOR="Blue"]With[/COLOR] Worksheets(arrSheets(i))
            [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] cell [COLOR="Blue"]In[/COLOR] .UsedRange
                [COLOR="Blue"]If[/COLOR] cell.Value [COLOR="Blue"]Like[/COLOR] "*" & "Other Vehicles" & "*" [COLOR="Blue"]Or[/COLOR] _
                   cell.Value [COLOR="Blue"]Like[/COLOR] "*" & "Class 57" & "*" [COLOR="Blue"]Then[/COLOR]
                   .Range(.Cells(cell.Row, 1), .Cells(Rows.Count, 1)).EntireRow.Delete
                [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
            [COLOR="Blue"]Next[/COLOR]
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
        [COLOR="Blue"]On[/COLOR] [COLOR="Blue"]Error[/COLOR] [COLOR="Blue"]GoTo[/COLOR] 0
    [COLOR="Blue"]Next[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]