VBA code - Autofilter and Deleting

[Mattandy55]

Hi all,

I have the below code:

ActiveCell.AutoFilter Field:=9, Criteria1:="<>"
ActiveSheet.AutoFilter.Range.Offset(6, 0).Rows.SpecialCells(xlCellTypeVisible).Delete

I am using it on multiple worksheets and it seems to work fine on the first two, but when I get to the third sheet I have an issue. What is happening is its filtering properly, my column headers are row 6, and sheet 3 has 4 records. So the code filters to row 11 and beyond, but when the delete aspect of the code kicks in, it leaves row 11 and deletes rows starting at row 12. Any idea why its not deleting the first row when the code runs? I'm a little stumped just because it works and deletes rows correctly on the first 2 sheets it runs on.

Thank you

 

[DanteAmor]

Hi @Mattandy55. Thanks for posting on MrExcel.

ActiveSheet.AutoFilter.Range.Offset(6, 0).Rows.SpecialCells(xlCellTypeVisible).Delete

You have an offset, that's why it doesn't delete from row 7.

-----------------------------------------------​

Try the following:

Sub deleterows()
  Dim lr As Long
 
  lr = Range("I" & Rows.Count).End(3).Row
  Range("A6:I" & lr).AutoFilter Field:=9, Criteria1:="<>"
  ActiveSheet.AutoFilter.Range.Offset(1, 0).EntireRow.Delete
  ActiveSheet.Range("I6").AutoFilter
End Sub

-----------------------------------------------​

If you want it for all sheets.

Sub deleterows_sheets()
  Dim lr As Long
  Dim sh As Worksheet
 
  For Each sh In Sheets
    lr = sh.Range("I" & Rows.Count).End(3).Row
    sh.Range("A6:I" & lr).AutoFilter Field:=9, Criteria1:="<>"
    sh.AutoFilter.Range.Offset(1, 0).EntireRow.Delete
    sh.Range("I6").AutoFilter
  Next
End Sub

-----------------------------------------------​

If you want it for some sheets:

Sub deleterows_some_sheets()
  Dim lr As Long
  Dim sh As Worksheet
 
  For Each sh In Sheets
    Select Case sh.Name
      Case "Sheet1", "Sheet2", "Sheet3", "etc"
        lr = sh.Range("I" & Rows.Count).End(3).Row
        sh.Range("A6:I" & lr).AutoFilter Field:=9, Criteria1:="<>"
        sh.AutoFilter.Range.Offset(1, 0).EntireRow.Delete
        sh.Range("I6").AutoFilter
    End Select
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​