How to delete only filtered rows ?

You can copy paste the below function in VBA editor and test it yourself with different columns and criteria.

Function delvisiblerows(col, criteria)

ActiveSheet.Range(“$A:$AI”).AutoFilter field:=col, Criteria1:= criteria

Set Rng = Application.Intersect(ActiveSheet.UsedRange, ActiveSheet.Range(Cells(2, 1), Cells(1048576, 100)))

If ActiveSheet.AutoFilter.Range.Columns(1).specialcells(xlCellTypeVisible).Cells.Count – 1 > 0 Then
Rng.EntireRow.Delete
End If

ActiveSheet.AutoFilterMode = False

End Function

 

How this works ?

ActiveSheet.Range(“$A:$AI”).AutoFilter field:=col, Criteria1:=”<>” & criteria

here we pass the column number and the criteria values from the macro routine.

This is then used around the If statement above. If any row is filtered as per the criteria match, then only the visible rows will be deleted.

The important function to remember here is .specialcells(xlCellTypeVisible).Cells property available in the excel vba.

e.g.
Say you have different user names in column A and you want to delete the data for user “Suresh”, you can pass the values in macro as below.

Sub DeleteVisibleRows()

Call delvisiblerows(1, “Suresh”)

MsgBox “Data for Suresh Deleted Sucessfully!”, vbInformation

End Sub

Thank you for reading. Happy Learning !

Please leave a reply if you like the article or if you need any help on excel

%d bloggers like this: