Delete rows based on values

[sh1pley]

I am trying to get the code to look down column G then delete any line that says "Maintenance" or "Online" etc

Do I need to write an If statement for each variable?

Range("G" & FirstRow).Select
ActiveCell.Offset(0, 0).Select

For i = 1 To FinalRow
If ActiveCell.Value = "Maintenance" Or "Online" Or "Walk-In" Or "Walk-in" Or "Non-Bookable" Or "Walk In" Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i

 

[VoG]

Try

For i = FinalRow To 1 Step -1
    If Range("G" & i).Value = "Maintenance" Or Range("G" & i).Value = "Online" Or Range("G" & i).Value = "Walk-In" Or Range("G" & i).Value = "Non-Bookable" Then
        Range("G" & i).EntireRow.Delete
    End If
Next i

 

[Paddy1979]

Ah, i see you already have a reply, oh well i might as well post this as i have typed it out

Dim dtx As Variant 

Application.ScreenUpdating = False 

dtx = Array("Maintenance"", "Online", "etc etc") 

For i = FINALROW To 1 Step -1 
  If Not IsError(Application.Match(Cells(i, 7), dtx, 0)) 
      Cells(i, 7).EntireRow.Delete 
  End If 
Next i 

Application.ScreenUpdating = True 

End Sub

[/code]

 

[Peter_SSs]

Yet another slightly different approach...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DelRows()
    <SPAN style="color:#00007F">Dim</SPAN> FinalRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    <SPAN style="color:#00007F">Const</SPAN> Unwanted <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = _
        "|Maintenance|Online|Walk-In|Walk-in|Non-Bookable|Walk In|"
    FinalRow = Range("G" & Rows.Count).End(xlUp).Row
    <SPAN style="color:#00007F">For</SPAN> i = FinalRow <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1
        <SPAN style="color:#00007F">If</SPAN> Replace(Unwanted, "|" & Cells(i, 7).Value & "|", "") <> Unwanted <SPAN style="color:#00007F">Then</SPAN>
            Rows(i).EntireRow.Delete
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>