Deleting Rows with a specific word

[Guzzlr]

For Each sht In ActiveWorkbook.Worksheets
    If sht.Visible And (sht.Name = "Ramp") Then
        sht.Activate
    End If
    
    For r = Cells(Rows.Count, "M").End(xlUp).Row To 7 Step -1
        If Cells(r, "M").Value <> "Ramp" Then Rows(r).Delete
    Next r
End If

Hello All.
I'm trying to delete an entire row, if the word "Ramp" does not appear in column "M", beguinning on row 7...but I cant seem to get my End If and Next r in the proper place
Can someone lead me in the right direction please
Thank you

 

[Fluff]

The final End If should be Next Sht

 

[Fluff]

Another way of doing it would be

Sub delramp()
   With Sheets("Ramp")
      With .Range("M7", .Range("M" & Rows.Count).End(xlUp))
         .AutoFilter 1, "<>Ramp"
         .Offset(1).SpecialCells(xlVisible).EntireRow.Delete
      End With
      .AutoFilterMode = False
   End With
End Sub

 

[dmt32]

Hi,
looking at posted code you are missing a Next sht

try this updated to your code

For Each sht In ActiveWorkbook.Worksheets
    If sht.Visible And UCase(sht.Name) = "RAMP" Then
    For r = sht.Cells(sht.Rows.Count, "M").End(xlUp).Row To 7 Step -1
        If UCase(sht.Cells(r, "M").Value) <> "RAMP" Then sht.Rows(r).Delete
    Next r
    End If
Next sht

your approach is not the quickest method but see if it works ok for you.

Dave

 

[Guzzlr]

The final End If should be Next Sht

Ohh...

Thanks

 

[Fluff]

You're welcome

 

[Guzzlr]

For Each sht In ActiveWorkbook.Worksheets
    If sht.Visible And UCase(sht.Name) = "RAMP" Then
    For r = sht.Cells(sht.Rows.Count, "M").End(xlUp).Row To 7 Step -1
        If UCase(sht.Cells(r, "M").Value) <> "RAMP" Then sht.Rows(r).Delete
    Next r
    End If
Next sht

Queston, Why use "UCase" if the word is lower case "Ramp"?
thanks for the help

 

[Guzzlr]

Queston, Why use "UCase" if the word is lower case "Ramp"?
thanks for the help

Never Mind, my RAMP is uppercase...My Bad

 

[dmt32]

Queston, Why use "UCase" if the word is lower case "Ramp"?
thanks for the help

to make search values case insensitive.

Dave

 

[Guzzlr]

For Each sht In ActiveWorkbook.Worksheets
If sht.Visible And UCase(sht.Name) = "RAMP" Then
    For r = sht.Cells(sht.Rows.Count, "M").End(xlUp).Row To 7 Step -1
        If UCase(sht.Cells(r, "M").Value) <> "RAMP" Then
        Intersect(Columns("M").SpecialCells(xlCellTypeBlanks).EntireRow, Columns("M:Q")).Delete xlUp
    Next r
End If
Next sht

So, getting back to this. I'm now trying to delete a range of columns M:Q, instead of the deleting the entire row.
I'm trying this code above, but having issues with it. Can someone lead me in the right direction please.
Thanks for the help