Looping rows in filtered table

Question

Does anyone have a good way of looping through rows in filtered tables?

I tend to need to use some data from a filtered table to populate another document, in this case a statement of accounts worksheet. Since the rows are non-continuous, my previous methods of populating data using the row number seem to be failing.

SOAwb is a separate workbook to be populated with data from the TSOA table, most of this code actually works but data from the filtered/hidden range is still included.

j is the first empty row on SOAwb, so at the end of the for loop when j=j+1 the next empty row repeats the population process

For k = 1 To TSOA.DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible).Count

SOAwb.Worksheets(1).Cells(j, 4) = TSOA.DataBodyRange(k, 6) 'debit amount

SOAwb.Worksheets(1).Cells(j, 5) = TSOA.DataBodyRange(k, 7) 'credit amt

SOAwb.Worksheets(1).Cells(j, 1) = TSOA.DataBodyRange(k, 3) 'Date

SOAwb.Worksheets(1).Cells(j, 2) = Right(TSOA.DataBodyRange(k, 3), 2) & "-" & TSOA.DataBodyRange(k, 2) 'Reference Number

Select Case TSOA.DataBodyRange(k, 1)

Case "INV"

    SOAwb.Worksheets(1).Cells(j, 3) = "Invoice"

Case "CR"

    SOAwb.Worksheets(1).Cells(j, 3) = "Credit"

Case Else

    MsgBox "Invalid Type"

End Select

j = j + 1

Next

It would be perfect if k was still the relative row # position on the table. Does anyone have any suggestions on how to do this, or alternative methods to populate data (row by row) to another workbook using a filtered range?

Answer 1

Hidden rows have Height=0

You'll have to loop over all used rows and process only the visible ones

For k = 1 To TSOA.DataBodyRange.Rows.Count

and

If SOAwb.Worksheets(1).Cells(j, 4).Height > 0 Then...

similar to this:

For k = 1 To TSOA.DataBodyRange.Rows.Count

    With SOAwb.Worksheets(1)

        If TSOA.DataBodyRange(k, 6).Height > 0 Then

            .Cells(j, 4) = TSOA.DataBodyRange(k, 6) 'debit amount
            .Cells(j, 5) = TSOA.DataBodyRange(k, 7) 'credit amt
            .Cells(j, 1) = TSOA.DataBodyRange(k, 3) 'Date
            .Cells(j, 2) = Right(TSOA.DataBodyRange(k, 3), 2) & "-" & _
                                TSOA.DataBodyRange(k, 2) 'Reference Number
            Select Case TSOA.DataBodyRange(k, 1)
                Case "INV": .Cells(j, 3) = "Invoice"
                Case "CR":  .Cells(j, 3) = "Credit"
                Case Else:  MsgBox "Invalid Type"
            End Select
        End If
    End With
    j = j + 1
Next

Answer 2

Use the range collection you already specify directly, rather than its count

Dim rCell as Range
For each rCell in TSOA.DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible)
    ' Access the cells as
    t.DataBodyRange.Cells(c.Row - t.HeaderRowRange.Row, ColumnNum)
Next