Useful tricks to filtered document using VBA.

Filter the sheet:

With sheetName
    .Range(Range).AutoFilter field:=2, Criteria1:="001*"
End With

Get value of first filtered cell:

Sub FirstVisibleCell()
   With Worksheets("Sheet1").AutoFilter.Range
       ActiveCell.Value2 = Range("C" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
    End With
End Sub

Delete only filtered rows (with header):

Worksheets("Sheet Name").Range("$A:$K").SpecialCells _
                (xlCellTypeVisible).EntireRow.Delete

Copy and paste what is filtered:

Worksheets("Sheet Name").AutoFilter.Range.Copy _
Destination:=Worksheets("Dest Sheet").Range("A1")

Copy and paste certain rows:

Worksheets("Sheet name").Range("range").SpecialCells(xlCellTypeVisible).Copy
Worksheets("Sheet name").Cells(1, 1).PasteSpecial xlPasteValues

Categorized in:

VBA Excel,

Last Update: May 18, 2024