Help With Delete Row Code

[Dazzawm]

I am a novice when writing code but have written the code below that looks for the word 'Bus' (as an example) in a cell and delete the entire row. How do I change the code so that it deletes the row if the word 'Bus' is amongst other data in the cell and not on its own. Thanks. Also I want the code to look for all case types (BUS, bus, Bus)

Sub Delete()
Application.ScreenUpdating = False
Dim A As Long
Dim B As Long
Range("R1").Select
A = Selection.CurrentRegion.Rows.Count
For B = 1 To A
If Selection.Value = "Bus" Then 'Change word when necessary
Selection.EntireRow.Delete
Else
Selection.Offset(1, 0).Select
End If
Next B
Application.ScreenUpdating = True
End Sub

 

[VoG]

You need to loop backwards when deleting rows. Try

For B = A To 1 Step -1
If LCase(Selection.Value) Like "* bus *" Then

 

[Dazzawm]

You need to loop backwards when deleting rows. Try

For B = A To 1 Step -1
If LCase(Selection.Value) Like "* bus *" Then

Thanks but that didnt delete anything.

 

[VoG]

Then maybe without the spaces but this would also delete busted for example

For B = A To 1 Step -1
If LCase(Selection.Value) Like "*bus*" Then

 

[Dazzawm]

Then maybe without the spaces but this would also delete busted for example

For B = A To 1 Step -1
If LCase(Selection.Value) Like "*bus*" Then

Thanks that worked, luckily I havent got the word busted! May I ask why I need to loop backwards when deleting rows please.

 

[VoG]

Imagine that rows 1 and 2 both contain bus. Loop forwards and you delete row 1. Row 2 becomes row 1 but your loop then looks at row 2 (which is now the old row 3) and misses row 1 (the old row 2).

If you loop backwards that doesn't happen.

 

[Dazzawm]

Thanks, could I ask another thing. Would the code change drastically if I wanted to delete all the rows that didnt have the word 'Bus'?

 

[VoG]

Try

If Not LCase(Selection.Value) Like "*bus*" Then

 

[Dazzawm]

Try

If Not LCase(Selection.Value) Like "*bus*" Then

That deleted every row!

 

[VoG]

Maybe

If InStr(LCase(Selection.Value), "bus") = 0 Then