Search for word and clear cell if it contains that word

0
votes

I have column "A1" which list names and some names ending with totals (subtotals). I need a vba code to go through each cell, and if cell contains the word "total", clear that cell and if not move on to the next one. Do this until cell before the cell that contains grand total.

Using the code below, it only looks for totals and not cells that contains more than that "Derek Smith Total". I would also need to add do it until the cell before "Grand Total". 

Sub testing101()

    Dim lRow As Long
    Dim iCntr As Long
    lRow = 130
    For iCntr = lRow To 1 Step -1
        If Cells(iCntr, 1).Value = "*total*" Then
           Cells(iCntr, 1).Select
           Selection.ClearContents
        End If
    Next

End Sub
2
vba

2 Answers

1
votes

You can use Like - 

Sub testing101()

    Dim lRow As Long
    Dim iCntr As Long
    lRow = 130
    For iCntr = lRow To 1 Step -1
        With Cells(iCntr, 1)
            If .Value Like "*total*" Then .ClearContents
        End With
    Next

End Sub
0
votes

Sub testing101()

Dim lRow As Long
Dim iCntr As Long
lRow = 130
For iCntr = lRow To 1 Step -1
    With Cells(iCntr, 1)
        If .Value Like "*total*" Then 
           if .Value <> "Grand Total" then.ClearContents
        End If
    End With
Next

End Sub