Saturday, September 17, 2011

Find and Delete using Autofilter

If you want to delete the mass data in one go . Snapshot below

Here is the code-

Sub find_delte_using_autofilter()
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Dim Z As Long

On Error Resume Next
' to remove the filtermode if
Sheets(1).AutoFilterMode = False
' to find the last row
Z = Sheets(1).Range("a1").End(xlDown).Row

' change the range , field and criteria as per your requirement
Sheets(1).Range("$A$1:$c" & Z).AutoFilter Field:=2, Criteria1:="del"
' delete the filtered data
Sheets(1).Range("$A$2:$c" & Z).SpecialCells(xlCellTypeVisible).Delete
On Error Resume Next
' remove autofilter
Sheets(1).AutoFilterMode = False
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
End Sub

No comments:

Post a Comment

Import data from SQL

Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...