PDA

View Full Version : Delete Row If Cell Doesn't Contain Word



dpmaki
06-13-2012, 12:28 PM
I'm looking for some VBA code that will delete the entire row if the word "Total" is not found in the cells ("B1:B"). Right now I have a handful of entries where total appears as "141000 Total". The number of course changes, but it's always 6 digits and the word Total is always at the end.

Thanks!!

dazwm
06-13-2012, 01:10 PM
Try this



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

dpmaki
06-13-2012, 01:17 PM
That deleted everything. I only want to delete the rows where B doesn't contain the word "Total"

CatDaddy
06-13-2012, 01:29 PM
Sub delete()
Dim cell as range
For each cell in range("B2:B" & Range("B" & rows.count).end(xlup).Row)
If not instr(cell.text, "Total")
cell.EntireRow.Delete
End if
next cell