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
ActiveSheet.AutoFilterMode = False
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.
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.
Call delvisiblerows(1, “Suresh”)
MsgBox “Data for Suresh Deleted Sucessfully!”, vbInformation
Thank you for reading. Happy Learning !