Store AutoFilter Row Numbers using VBA

Question

How do I store and retrieve the row numbers returned from an AutoFilter action using VBA? For example, I used @brettdj code from this question (see code below) to delete all rows with "X" under column B. Now I need to store the row numbers with X (B4,B6,B9 - see screen shots below) because I need to delete the same rows on other sheets in the same workbook.

Before AutoFilter After AutoFilter

Sub QuickCull()
    Dim ws As Worksheet
    Dim rng1 As Range
    Set ws = Sheets("Sheet1")
    Set rng1 = ws.Range(ws.[b2], ws.Cells(Rows.Count, "B").End(xlUp))
    Application.ScreenUpdating = False
    With ActiveSheet
            .AutoFilterMode = False
            rng1.AutoFilter Field:=1, Criteria1:="X"
            rng1.Offset(1, 0).EntireRow.Delete
            .AutoFilterMode = False
        End With
    Application.ScreenUpdating = True
End Sub

After applying the filter, step *backwards* through the cells in `rng1`: if the `.RowHeight` is >0 then delete that row on your other sheets. — Tim Williams, Nov 30 '12 at 23:30

score 3 · Accepted Answer · edited May 23 '17 at 12:28

Using the code from Is it possible to fill an array with row numbers which match a certain criteria without looping? you could return these rows quickly without the AutoFilter

For example, this code will return a range of rows where X is found within B2:B50000

Sub GetEm()
Dim StrRng As String
StrRng = Join(Filter(Application.Transpose(Application.Evaluate("=IF(B2:B50000=""X"",""B""&ROW(B2:B50000),""X"")")), "X", False), ",")
If Len(StrRng) > 0 Then MsgBox Range(StrRng).EntireRow.Address & " could be deleted elsewhere"
End Sub

Store AutoFilter Row Numbers using VBA

1 Answers1