Looping through a filtered Excel excel table-CodePudding

For demonstration purposes, I have simplified my excel-formatted table to only 3 columns and 4 rows. What I need to do is loop through this simple table when it is filtered and on few occasions loop through the unfiltered table. I have looked all over the internet, and the closest thing I've gotten to my solution (in a rather cumbersome way) is through the use of the .SpecialCells(xlCellType).Areas property. But ironically, this solution didn't work when the table happens to be unfiltered.

Here's my entire, unfiltered table:

As a proof of concept, I'm just filtering to where Gender = "M". So here's how my filtered data looks like.

Now I just want to loop through this table and reference the cells that contain information, so in this table, I want to be able to print the following in the Debug.Print

I can't quite get the outputs that I want and the following code shows the three iterations that I have that serve to illustrate the point. What am I going wrong? This is is driving me nuts. Any help is greatly appreciated.

Sub filter_loop()
Dim people_table As ListObject

Set people_table = Worksheets("Sheet1").ListObjects("Table13")
Set people_table_visible = people_table.DataBodyRange.SpecialCells(xlCellTypeVisible)

' Set of iterations

' Iteration 1
For Each rngArea In people_table_visible.Areas
    For rngRow = 1 To rngArea.Rows.Count
        Debug.Print rngArea.Cells(1, 1) & " is " & rngArea.Cells(1, 2)
    Next rngRow
Next
' PROBLEM here is that it doesn't work when the table is not filtered

' Iteration 2

filtered_rows_r = people_table.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count

For r = 1 To filtered_rows_r
    Debug.Print people_table.DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(r, 1) & " is " & people_table.DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(r, 2)
Next r
'PROBLEM here is I get back "Diego is 23" and that's it, because filtered_rows_r returns 1 when it should return 2

' Iteration 3
filtered_rows_r2 = people_table.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count   1

For r2 = 1 To filtered_rows_r2
    Debug.Print people_table.DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(r2, 1) & " is " & people_table.DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(r2, 2)
Next r2
'PROBLEM here is I get back "Diego is 23" and "Maria is 12", so it's not respecting the filtering

End Sub

CodePudding user response：

Discontinuous (multi-area) ranges can't be looped through using (eg)

For x = 1 to rng.[Areas/Cells/Rows].Count  
    'do something with  [Areas/Cells/Rows](x)
Next

A For Each approach is the most robust way to do it.

This works whether or not the table is filtered (as long as all table columns are visible):

Sub filter_loop()
    Dim people_table As ListObject, rngVis As Range, c As Range, rw As Range
    
    Set people_table = Worksheets("Sheet1").ListObjects("Table13")
    
    On Error Resume Next 'ignore error in cases where there are no visible rows
    Set rngVis = people_table.DataBodyRange.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0      'stop ignoring errors
    
    If rngVis Is Nothing Then Exit Sub
    
    For Each rw In rngVis.Rows
        Debug.Print rw.Cells(1).Value & " is " & rw.Cells(2).Value
    Next rw
End Sub