Excel VBA delete row where cell contains search string

0
votes

Trying to loop through a column, searching for "Total" within cells that also contain other text (e.g., "January Total", "Boston Total", "Office Total", "Company ABC Total"), and deleting entire rows where "Total" is found. Here's what I have (r and rcell are defined as range objects):

Set r = Range(Cells(4, "B"), Cells(LastRow, "B"))
For Each rcell In r
    If Not Excel.WorksheetFunction.Search("Total", rcell) = "#VALUE!" Then
    Rows(target.Row).Delete
    Else
    'do nothing
    End If
Next

I'm trying to pursue the logic that says, "If the cell doesn't contain 'Total', it will return an error. So, for all the cells that don't return an error, delete the row." My syntax is clearly wrong. I've visited a number of similar questions here on the site and haven't been able to substitute working code.

In standard Excel formulas, this would be an IF(ISERROR(SEARCH(...))...).

Thank you very much for the help.

2
vbaloopsexcelforeachworksheet-function
A different approach. Turn on the macro recorder, filter the column to "Total," delete the visible rows, turn off the macro recorder, and edit the resulting code. – Doug Glancy
Thanks, Doug. Following your suggested steps produced code with an explicit array listing all of the records being filtered, which doesn't seem like it can be made dynamic. I didn't see any code related to my filter based on a search for "Total" (which I did while recording, but the result was the aforementioned array). Did I make a mistake somewhere? – Ephs05msm

2 Answers

1
votes

Just in case you come back this way, I thought I'd demonstrate what I meant in my comment. It's true that the Macro Recorder creates a literal array of all the values that contain "Total." Your generated code looked something like this, I'll bet:

Sub Macro1()
    ActiveSheet.Range("$B$3:$B$29").AutoFilter Field:=1, _
    Criteria1:=Array("Boston Total", "Chicago Total", "December Total", "February Total", _
        "January Total", "January Total Total", "November Total", "October Total", _
        "October Total Total", "San Francisco Total"), Operator:=xlFilterValues
    Rows("16:29").Select
    Selection.ClearContents
End Sub

The trick is to replace that array of values with a single value that uses wildcards, i.e., "*Total*". Combining that with your LastRow and other variables, I come up with:

Sub DeleteTotals()
Dim LastRow As Long
Dim r As Excel.Range

With ActiveSheet
    LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
    Set r = .Range(Cells(4, "B"), Cells(LastRow, "B"))
End With
With r
    .AutoFilter Field:=1, Criteria1:="*Total*", Operator:=xlFilterValues
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub

This kind of code is faster than looping. It may not matter if you don't have much data, but it's good to know you can do it this way.

0
votes

Consider:

Sub marine()
    Dim r As Range, rDelete As Range
    Set rDelete = Nothing
    LastRow = 100
    Set r = Range(Cells(4, "B"), Cells(LastRow, "B"))
    For Each rcell In r
        v = CStr(rcell.Value)
        If InStr(1, v, "Total") > 0 Then
            If rDelete Is Nothing Then
                Set rDelete = r
            Else
                Set rDelete = Union(r, rDelete)
            End If
        End If
    Next rcell

    If rDelete Is Nothing Then
    Else
        rDelete.EntireRow.Delete
    End If
End Sub

Use your own code to calculate LastRow

EDIT#1:

Here is the corrected version :

Sub marine()
    Dim r As Range, rDelete As Range
    Set rDelete = Nothing
    LastRow = 100
    Set r = Range(Cells(4, "B"), Cells(LastRow, "B"))
    For Each rcell In r
        v = CStr(rcell.Value)
        If InStr(1, v, "Total") > 0 Then
            If rDelete Is Nothing Then
                Set rDelete = rcell
            Else
                Set rDelete = Union(rcell, rDelete)
            End If
        End If
    Next rcell

    If rDelete Is Nothing Then
    Else
        rDelete.EntireRow.Delete
    End If
End Sub