Delete the Filtered Data after Filtering with the AutoFilter
Assuming that the sheet containing the data is the active sheet, and that the data has been filtered with the AutoFilter, the following macro deletes the filtered data...
'Force the explicit declaration of variables
Option Explicit
Sub DeleteFilteredData()
'Declare the variables
Dim Rng As Range
Dim CellCount As Long
Dim Msg As String
With ActiveSheet.AutoFilter.Range
'For Excel 2007 and earlier, check for the SpecialCells limitation
If Val(Application.Version) < 14 Then
On Error Resume Next
CellCount
= .Columns(1).SpecialCells(xlCellTypeVisible) _
.Areas(1).Cells.Count
On Error GoTo 0
If CellCount = 0 Then
Msg
= "The SpecialCells limit of 8,192 areas has been "
Msg = Msg & vbNewLine
Msg
= Msg & "exceeded for the filtered value."
Msg
= Msg & vbNewLine & vbNewLine
Msg
= Msg & "Sort the data, and try again!"
MsgBox
Msg, vbExclamation, "SpecialCells Limitation"
GoTo ExitTheSub
End If
End If
'Set the range for the first column of the filtered data,
'excluding the header row
On Error Resume Next
Set Rng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
'If filtered data exists, delete the entire row for the filtered data
If Not Rng Is Nothing Then
Rng.EntireRow.Delete
Else
MsgBox
"No records are available to delete...", vbExclamation
End If
End With
ExitTheSub:
'If the active sheet is in filter mode, make all the data visible
With ActiveSheet
If .FilterMode Then .ShowAllData
End With
End Sub
Where to Put the Code
- Open the workbook in which to store the code.
- Open the Visual Basic Editor (Alt+F11).
- Insert a regular module (Insert > Module).
- Copy/paste the above code into the module.
- Return to Microsoft Excel (Alt+Q).
- Save the workbook.
How to Use the Macro
- Display the Macro dialog box (Alt+F8).
- Click/select the macro called "DeleteFilteredData".
- Click/select "Run".
Last Update: May 11, 2012