Iterating on a list object's rows, how can I access the entire row?

Question

I come to you because VBA literature online does not show many results for when dealing with Tables and list objects.

With the following code, I add the list object items to a list box in a user form. I iterate through the list object's rows. But I need to validate wether the row is hidden as sometimes there will be filters on the table in the spreadsheet:

With Main
    .Clear
    Dim i As Long
    For i = 1 To tblDataMaster.ListRows.Count
        If tblDataMaster.Row(i).Hidden = False Then
            .AddItem
            Dim j As Integer
            For j = 0 To 9
                .List(.ListCount - 1, j) = tblDataMaster.DataBodyRange(i, (j + 5))
            Next j
        End If
    Next i
    End With

As written of course, the code won't work since .Row is not a property of the list object. But just to illustrate, the If statement needs to validate if that row is hidden or not. If it is not, then it will populate the list box with it.

Something like .DataBodyRange(i,1) is not working either.

Any help, greatly appreciated.

Answer 1

The key is to use ListRow.Range.

Dim tblRow As ListRow
For Each tblRow In tblDataMaster.ListRows
    If Not tblRow.Range.EntireRow.Hidden Then
        ...
    End If
Next

Or if iterating by index:

For i = 1 To tblDataMaster.ListRows.Count
    If Not tblDataMaster.ListRows(i).Range.EntireRow.Hidden Then
        ...
    End If
Next