Delete filtered/visible rows except headers in a table

[aghaffar82]

Hello Sirs,

I am filtering data from multiple columns with multiple criteria (manually), but want to delete the filtered out (visible) rows, except headers, with VBA.

The reason being I want to do this because my data table has around 1 million rows and once I am done with filtering there are still 400k-500k rows which take a lot of time deleting manually.

Thank you so much in advance for your kind help.
Best Regards

 

[juddaaaa]

Run this after you've applied the filter

You might need to change the Worksheet name and range to suit your setup

Sub DeleteFilteredRows()
   
    Dim last_row As Long
   
    Application.ScreenUpdating = False
   
    With Sheets("Sheet1")
        last_row = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
        .Range("A2", "A" & last_row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
   
    Application.ScreenUpdating = True

End Sub

 

[aghaffar82]

Run this after you've applied the filter

You might need to change the Worksheet name and range to suit your setup

Sub DeleteFilteredRows()
  
    Dim last_row As Long
  
    Application.ScreenUpdating = False
  
    With Sheets("Sheet1")
        last_row = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  
        .Range("A2", "A" & last_row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
  
    Application.ScreenUpdating = True

End Sub

Thank you

I will update you Sir, Once I have it a try. Regards