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

 

[Dazzawm]

Maybe

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

Nope, that done the same.

 

[Dazzawm]

Nope, that done the same.

I think it is looking for bus in lower case when the word could be in all case sizes as stated in an earlier post. I do know in this particular case they are all upper case hence deleting everything. How do you amend the code to cater for all case sizes?

 

[VoG]

Before

Excel Workbook
	R
1	a bus
2	bus
3	busted
4	a bus
5	s
6	d
7	busty
8	buses
Sheet3

Sub Delete()
Application.ScreenUpdating = False
Dim A As Long
Dim B As Long
Range("R1").Select
A = Selection.CurrentRegion.Rows.Count
For B = A To 1 Step -1
If InStr(LCase(Selection.Value), "bus") = 0 Then
Selection.EntireRow.Delete
Else
Selection.Offset(1, 0).Select
End If
Next B
Application.ScreenUpdating = True
End Sub

After

Excel Workbook
	R
1	a bus
2	bus
3	busted
4	a bus
5	busty
6	buses
7
8
9
10
11
Sheet3

 

[Dazzawm]

I think it is looking for bus in lower case when the word could be in all case sizes as stated in an earlier post. I do know in this particular case they are all upper case hence deleting everything. How do you amend the code to cater for all case sizes?

Please?

 

[VoG]

My results with all cells in capitals

Excel Workbook
	R
1	A BUS
2	BUS
3	BUSTED
4	BUSES
Sheet3

 

[Dazzawm]

Doesnt the Lcase look for lower case only? I changed the code to Ucase and it left what I needed. But in some cases the word will be in a mixture of cases.

 

[VoG]

No, it is a case-insensitive comparison. It is looking for bus in the cell's value converted to lower case. If it isn't found then the row is deleted.

 

[Dazzawm]

So if BUS is in upper case it changes it to lower case then deletes all the other rows.

 

[VoG]

It doesn't change the value in the cell. In code it uses LCase to convert to lower case (in memory, not on the sheet) then uses InStr to see if bus is present.

 

[Dazzawm]

Thanks for all your help again VoG.