VBA - How to delete AutoFiltered rows except for the header?

ZeePrime

New Member
Joined
Jul 7, 2010
Messages
16
Hi,
how do I select (in VBA) all the rows that were filtered out by autofilter (using VBA code) and delete them leaving just header. I just can't figure out how to select entire rows when the data is filtered...
Thanks for your hints!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Say your header is in row 1 and your data starts in column A

Code:
Sub test()
    Range("A2:A100").SpecialCells(xlCellTypeVisible).EntireRow.Delete
End Sub
Of course, you might have to extend the range to cover the entirity of the filtered range, but it's best to extend it at least a few rows beyond, so there's always some visible rows, otherwise the .specialcells() will error out.

HTH
 
Upvote 0
Had a thought:

Code:
Sub test()
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    If lr > 1 Then
        Range("A2:A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End If
End Sub
Might be a bit smarter, as this will do nothing if there are no rows showing.
 
Upvote 0
'Below is the complete program to delte only the visible filtered rows from a selected range.
If ActiveSheet.AutoFilterMode Then Selection.AutoFilter

ActiveCell.CurrentRegion.Select 'selects the current region while activecell being one the header cells

With Selection
.AutoFilter
.AutoFilter Field:=2, Criteria1:="404665" ' filters out column2 for values 404665
.Offset(1, 0).Select 'excludes the header row from the selected range
End With

With Selection
.SpecialCells(xlCellTypeVisible).EntireRow.Delete 'deletes all the filtered visible rows.
End With
 
Upvote 0
WITH CORRECTION
Code:
Option Explicit
Sub FilterData()
    
'Below is the complete program to delte only the visible filtered rows from a selected range.
    
    If ActiveSheet.AutoFilterMode Then Selection.AutoFilter
    
    ActiveCell.CurrentRegion.Select
    
    With Selection
        .AutoFilter
        .AutoFilter Field:=2, Criteria1:="404665"
        .Offset(1, 0).Select
    End With
    
    Dim numRows As Long, numColumns As Long
    numRows = Selection.Rows.Count
    numColumns = Selection.Columns.Count
    
    Selection.Resize(numRows - 1, numColumns).Select
    
    With Selection
  
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    


End Sub
 
Last edited by a moderator:
Upvote 0
To delete rows that have been filtered out:
Code:
Sub DeleteFilteredOutRows()
'
' DeleteFilteredOutRows Macro
'
    Dim x As Integer, HelperC As Integer, LastRow As Integer


    'Find LastRow
    Range("A1").Select
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    'Add Helper Column to identify if visible
    Range("A1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    HelperC = ActiveCell.Column ' HelperC = Column number of helper column
    ActiveCell.Value = "Visible?"
    
    'If visible, add 1 to Visible column
    For x = 2 To LastRow
         If Rows(x).EntireRow.Hidden Then
         Else
           Cells(x, HelperC).Value = 1
        End If
    Next x
     
    'If not visible(Visible column <> 1) then delete row
    For x = 2 To LastRow
        If Cells(x, HelperC).Value <> 1 Then
            Rows(x).EntireRow.Delete
       End If
    Next x
     
    Columns(HelperC).EntireColumn.Delete 'Delete Helper Column
    Range("A1").Select ' Select cell A1
    
    ' Removes filters
    On Error Resume Next
        ActiveSheet.ShowAllData
    
End Sub
 
Last edited by a moderator:
Upvote 0
Found a bug. Here's the corrected code.
Code:
Sub DeleteFilteredOutRows()
'
' DeleteFilteredOutRows macro
'
    Dim x As Integer, HelperC As Integer, LastRow As Integer
 
    'Find LastRow
    Range("A1").Select
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
   
    'Add Helper Column to identify if visible
    Range("A1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    HelperC = ActiveCell.Column ' HelperC = Column number of helper column
    ActiveCell.Value = "Visible?"
   
    'If visible, add 1 to Visible column
    For x = 2 To LastRow
         If Rows(x).EntireRow.Hidden Then
         Else
           Cells(x, HelperC).Value = 1
        End If
    Next x
   
    ' Removes filters
    On Error Resume Next
        ActiveSheet.ShowAllData
    
    'If not visible(Visible column <> 1) then delete row
    For x = 0 To (LastRow - 2)
        If Cells(LastRow - x, HelperC).Value <> 1 Then
            Rows(LastRow - x).EntireRow.Delete
       End If
    Next x
    
    Columns(HelperC).EntireColumn.Delete 'Delete Helper Column
    Range("A1").Select ' Select cell A1
   
End Sub
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top