How To Delete Filtered Rows In Excel Vba

In Excel VBA, you can easily delete filtered rows using the following steps:

1. First, you need to apply filters to the data range where you want to delete rows. You can do this by selecting the range and using the “AutoFilter” method.

      Worksheets("Sheet1").Range("A1:D10").AutoFilter Field:=1, Criteria1:="Delete"
    

This code applies a filter to the range A1:D10 where the value in the first column equals “Delete”.

2. Next, you can use the “SpecialCells” method with the “xlCellTypeVisible” parameter to select only the visible (filtered) cells in the range.

      Dim visibleRange As Range
      Set visibleRange = Worksheets("Sheet1").Range("A1:D10").SpecialCells(xlCellTypeVisible)
    

This code selects only the visible cells in the range A1:D10.

3. Finally, you can use the “Delete” method to delete the entire rows of the selected range.

      visibleRange.EntireRow.Delete
    

This code deletes all the visible (filtered) rows from the worksheet.

Here’s a complete example that combines these steps:

    Sub DeleteFilteredRows()
        Dim visibleRange As Range
        
        ' Apply filter
        Worksheets("Sheet1").Range("A1:D10").AutoFilter Field:=1, Criteria1:="Delete"
        
        ' Select visible range
        Set visibleRange = Worksheets("Sheet1").Range("A1:D10").SpecialCells(xlCellTypeVisible)
        
        ' Delete visible rows
        visibleRange.EntireRow.Delete
        
        ' Clear filter
        Worksheets("Sheet1").AutoFilterMode = False
    End Sub
  

The above code applies a filter to the range A1:D10, deletes the filtered rows, and then clears the filter.