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 !