[SOLVED] Macro to automatically delete row with the word "total"

Macro to automatically delete row with the word "total"

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!

Solution

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