Create a function to delete only the visible rows after data filter ?

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

_________________________________________________________________________________

Happy learning !

Also check out my post on how to find first and last date of any month.