CODEWITHSUNDEEP
vbaexcel
Greedo
Greedo

Reputation: 5543

How to skip to nth element of for next loop

I'm trying to return the nth visible element of a filtered table in Excel. Current methods tell me to use the SpecialCells(xlCellTypeVisible) method, then loop through the elements whilst counting them with a For Each... Next loop.

This seems sub-optimal; I don't want to know about the first 19 ranges, only the 20th. Is there any way to skip straight to the 20th element of a loop?

Or maybe loop through the .Areas and count their sizes; meaning you only need to step into the Area which contains the nth element, then sub-loop within that area to locate the element precisely. Would this be faster?

Of course, if you can think of a better way to get the nth element of a filtered table, that'd be good too!

My code to get the table range is:

Dim inptTbl As Range
Set inptTbl = ThisWorkbook.Sheets("compiled").ListObjects("Table2").ListColumns("Company").Range.SpecialCells(xlCellTypeVisible)

Then to loop through, well I haven't written that yet, but I guess:

i=0
Dim r As Range
For Each r in inptTbl
    i=i+1
    If i = 20 Then Exit For
Next r
Debug.Print r.Address

Looping through areas as described:

Set inptTbl = ThisWorkbook.Sheets("compiled").ListObjects("Table2")
Set test = inptTbl.ListColumns("Company").Range.SpecialCells(xlCellTypeVisible)

runningTot = 0
arIndex = 1
Do Until test.Areas(arIndex).Cells.Count + runningTot > 20
    runningTot = runningTot + test.Areas(arIndex).Cells.Count
    arIndex = arIndex + 1
Loop
i = 0
Dim r As Range
For Each r In test.Areas(arIndex)
    If i = 20 - runningTot Then Exit For
    i = i + 1
Next r
Debug.Print r.Address

Upvotes: 3

Views: 594

Answers (3)

CallumDA
CallumDA

Reputation: 12113

Final Update

As you mentioned you are looking for the most efficient way to go, I'd loop through the areas rather than individual cells.

Sub test2()
    Dim a As Range, rng As Range
    Dim n As Long, total As Long, adj As Long

    n = 20
    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:A1000").SpecialCells(xlCellTypeVisible)

    For Each a In rng.Areas
        adj = n - total
        total = total + a.Cells.Count
        If total >= n Then
            Debug.Print a.Cells(adj, 1).Address
            Exit Sub
        End If
    Next a
End Sub

Update #2

In the interest of keeping this answer relatively clean, I deleted my previous efforts.

Finally, and still using xlCellTypeVisible, this should work in any case. It adjusts the size of your range until there are 20 visible cells and then returns the last cell in that range.

Sub test()
    Dim rng As Range, r As Range
    Dim n As Long

    n = 20 'update for your required value of n

    With ThisWorkbook.Worksheets("Sheet1") 'relevant sheet name
        Set r = .Range("A1") 'where to start?
        Set rng = r.Resize(n, 1)
    End With

    While rng.SpecialCells(xlCellTypeVisible).Count < n
        Set rng = rng.Resize(rng.Rows.Count + 1, 1)
    Wend

    Debug.Print rng.Cells(rng.Rows.Count + r.Row - 1, r.Column).Address

End Sub

Upvotes: 2

user4039065
user4039065

Reputation:

A filtered range could have many discontiguous Areas (see Get Excel filter results into VBA array). If you are looping through the visible cells, then use areas but if yu want to jump to the 20th visible elemen t, just run through a For Next loop until you meet the extent you want to retrieve.

Dim r As Long, v As Long

v = 20

With Selection
    For r = 1 To .Rows.Count
        v = v + (Not .Cells(r).EntireRow.Hidden)
        If v = 0 Then Exit For
    Next r

    Debug.Print .Cells(r).Value
End With

I've used Selection here for expediency; you can retrieve the actual cell range from your table.

Upvotes: 2

Tim Wilkinson
Tim Wilkinson

Reputation: 3791

Would need to see your full code ideally, but instead of using For Each....Next, you could count the visible cells and do something like

Sub test()
Dim vcells as Long

vcells = Activesheet.Usedrange.SpecialCells(xlCellTypeVisible).Rows.Count

For i = 20 to vcells
    `your code here
Next i

End Sub

Upvotes: 1

Related Questions