Macro VBA Code to delete rows in a filtered list

[RichBRich]

I recorded a macro to prepare/format and clean up an exported set of data. This data set changes is size (number of rows) each month I run the report and export the data. I believe my macro to remove the Grand Totals and Salesperson Total contained in the raw data of the spreadsheet deletes specific row numbers (276:276 and 624:634). I want to delete the rows that appear in the filter criteria regardless of their row number. The following is the current VBA code:

ActiveSheet.Range("$A$3:$G$821").AutoFilter Field:=1, Criteria1:= _
"Grand Total"
ActiveWindow.SmallScroll Down:=-9
Rows("276:276").Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$3:$G$820").AutoFilter Field:=1, Criteria1:= _
"Salesperson Total"
Rows("624:634").Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$3:$G$809").AutoFilter Field:=1
End Sub

 

[JLGWhiz]

Selection.SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp

 

[JLGWhiz]

Sorry, you wanted the whole enchilada.

ActiveSheet.Range("$A$3:$G$821").AutoFilter Field:=1, Criteria1:= _
"Grand Total"
Ra;nge("A3", Cells(Rows.Count, 1).End(xlUp)).SepecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutofilterMode = False
ActiveSheet.Range("$A$3:$G$820").AutoFilter Field:=1, Criteria1:="Salesperson Total"
Range("A3", Cells(Rows.Count, 1).End(xlUp)).Resize(, 8).SpecialCells(xlCellTypeVisible).Delete xlShiftUp
ActiveSheet.AutoFilter
 End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

I have taken a bit of a guess that "Grand Total" and "Salesperson Total" are the only two types of "Total" in column A.
If so, try (in a copy of your data)

Sub DeleteFilteredRows()
  With Range("A3", Range("A" & Rows.Count).End(xlUp)).Resize(, 7)
    .AutoFilter Field:=1, Criteria1:="* Total"
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    .AutoFilter Field:=1
  End With
End Sub

If there are other types of "Total" rows that should remain then you can still do both at once ..

Sub DeleteFilteredRows_v2()
  With Range("A3", Range("A" & Rows.Count).End(xlUp)).Resize(, 7)
    .AutoFilter Field:=1, Criteria1:="Grand Total", Operator:=xlOr, Criteria2:="Salesperson Total"
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    .AutoFilter Field:=1
  End With
End Sub

Note that with deleting filtered rows there is no need to specify visible cells only as that is what happens anyway.
And when deleting whole rows there is no need to specify shifting rows up as again, that is what happens anyway.

 

[RichBRich]

Awesome! It worked. Thanks to both of you for the excellent and accurate recommendations. The macro even ran more quickly with improved code!

 

[Peter_SSs]

Awesome! It worked. Thanks to both of you ...

You're very welcome. Thanks for the follow-up.

 

[JLGWhiz]

Happy to contribute,
Regards, JLG