Macro to automatically delete row with the word "total"

Question

I need help on a macro to automatically delete a whole row with the word "total". There is number next to the total, and I want to delete the whole row. Issue is, every week the name changes, hence I need it to be dynamic. Attached is my code example.

'
' Macro4 Macro
'

'
    Selection.AutoFilter
    ActiveSheet.Range("$C$1:$C$60").AutoFilter Field:=1, Criteria1:=Array( _
        "FR083 Total", "FR087 Total", "FR088 Total", "FR089 Total", "FR091 Total", _
        "FR092 Total", "FR093 Total", "FR097 Total", "FR098 Total"), Operator:= _
        xlFilterValues
    Selection.Delete Shift:=xlToLeft
End Sub

Kindly help on this, thank you!

Answer 1

Microsoft documentation:

Range.EntireRow property (Excel)

Range.SpecialCells method (Excel)

Option Explicit

Sub DeleteTotalRow()
    Dim c As Range
    With ActiveSheet
        If .AutoFilterMode Then .AutoFilter.ShowAllData
        With .Range("$C$1:$C$60")
            ' Filter the cells contain `total`
            .AutoFilter Field:=1, Criteria1:="=*total*", Operator:=xlAnd
            ' Get the visible cells
            Set c = .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible)
            If Not c Is Nothing Then
                ' Delete the whole rows
                c.EntireRow.Delete
            End If
        End With
        ' Show all data
        .AutoFilter.ShowAllData
    End With
End Sub

Answer 2

Delete Rows Matching a Wild Character Pattern Using AutoFilter

Before

After Select

After Delete

• It is assumed that the data is in table format (no empty rows or columns with one row of headers) and starts in A1 (CurrentRegion) as illustrated in the screenshots.
• If that's not the case, use Set rg = ws.UsedRange. If you don't have data in column A, use 2, or in column B, use 1 instead of 3.
• When done testing, replace the Select part with the Delete part.

Sub DeleteTotalRows()

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    If ws.AutoFilterMode Then ws.AutoFilterMode = False

    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion
    Dim drg As Range: Set drg = rg.Resize(rg.Rows.Count - 1).Offset(1)
        
    rg.AutoFilter Field:=3, Criteria1:="FR???? Total"
    
    Dim vrg As Range
    On Error Resume Next
        Set vrg = drg.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    ws.AutoFilterMode = False
    
    If Not vrg Is Nothing Then vrg.Select ' vrg.Delete xlShiftUp '
       
End Sub