CODEWITHSUNDEEP
vbaexcel
quizzical_panini
quizzical_panini

Reputation: 434

Fast way to loop through filtered list in reverse?

I have a sheet with a large number of auto-filtered rows (>200,000). I'm trying loop 'upwards' through a column until I find the first cell that's different from the current cell. I can loop 'downwards' through through visible cells by using:

For Each cl In rng.SpecialCells(xlCellTypeVisible)
    'check for different value
Next cl

I can also loop 'upwards' skipping over hidden rows using:

For i = rng.Count To 1 Step -1
    If rng.Cells(i).EntireRow.Hidden Then
    'do nothing
    ElseIf 'check different value
    End If
Next i

But with a large number of hidden rows this can take a while to skip over all of them even if there are only a couple of hundred visible rows. I've tried using rng.SpecialCells(xlCellTypeVisible) and stepping backwards through them but it seems to also go through hidden cells.

  1. Is there a way to reverse the order of a For Each loop?
  2. Is there a faster way to do this?

Thanks

Upvotes: 3

Views: 2208

Answers (2)

Gary's Student
Gary's Student

Reputation: 96773

You could build a Collection of the visible cells and then extract them in reverse:

Sub Backwards()
    Dim N As Long, col As Collection, RR As Range, r As Range
    Dim i As Long
    Set RR = Intersect(ActiveSheet.UsedRange, Range("A:A").Cells.SpecialCells(xlCellTypeVisible))
    Set col = New Collection
    For Each r In RR
        col.Add (r.Address)
    Next r
    N = col.Count

    For i = N To 1 Step -1
        Set r = Range(col(i))
        MsgBox r.Address
    Next i
End Sub

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166511

Sub Tester()
    Dim x As Long, n As Long
    Dim a() As Long
    Dim rng As Range, c As Range, vis As Range
    Dim sht As Worksheet

    Set sht = ActiveSheet
    Set rng = sht.Range("A1:A1000")

    Set vis = rng.SpecialCells(xlCellTypeVisible)
    n = vis.Cells.Count
    ReDim a(1 To n)
    x = 1

    For Each c In vis.Cells
        a(x) = c.Row
        x = x + 1
    Next c

    For x = n To 1 Step -1
        Debug.Print a(x), sht.Cells(a(x), 1)
    Next x
End Sub

Upvotes: 3

Related Questions