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 procedure deletes the filtered data...
'Force the explicit declaration of variables
Option Explicit
Sub DeleteFilteredData()
'Declare the variables
Dim rngFilt As Range
Dim CellCount As Long
Dim Msg As String
'If the data has not been filtered with the AutoFilter, exit the sub
With ActiveSheet
If .AutoFilterMode = False Or .FilterMode = False Then
MsgBox
"Please filter the data with the AutoFilter, and try again!"
Exit Sub
End If
End With
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 & "Tip: Sort the data, and try again!"
MsgBox
Msg, vbExclamation, "SpecialCells Limitation"
GoTo ExitTheSub
End If
End If
'Set the filtered range
On Error Resume Next
Set rngFilt = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
'Delete the filtered data
If Not rngFilt Is Nothing Then
rngFilt.EntireRow.Delete
Else
MsgBox
"No records are available to delete...", vbExclamation
End If
End With
ExitTheSub:
'Clear the filter
ActiveSheet.ShowAllData
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".