How to get all filtered data rows in excel (with hidden columns)?

Question

My data sheet has filters and hidden columns. When filter(s) applies, I need to loop through all filtered data. I use: Excel.Range visibleRange = this.UsedRange.SpecialCells(XlCellType.xlCellTypeVisible, missing) as Excel.Range;

Now visibleRange.Rows.Count is 0; use foreach loop "foreach(Excel.Range row in visibleRange.Row)" row doesn't have all the columns, chopped from the first hidden column.

How could we loop through the filtered rows?

Answer 1

I wouldn't use the SpecialCells property at all. Just iterate through every row in the UsedRange and check the Hidden property as you go.

Not sure what language you are using but here's an example in VBA:

Dim rowIndex As Range

With Worksheets("Sheet1")
    For Each rowIndex In .UsedRange.Rows
        If (rowIndex.Hidden) Then
            ' do nothing - row is filtered out
        Else
            ' do something
        End If
    Next rowIndex
End With

Each row (or rather each Range object referenced by rowIndex) will contain all of the columns including the hidden ones. If you need to determine whether or not a column is hidden then just check the Hidden property but remember that this only applies to entire columns or rows:

Dim rowIndex As Range
Dim colNumber As Integer

With Worksheets("Sheet1")
    For Each rowIndex In .UsedRange.Rows
        If (rowIndex.Hidden) Then
            ' do nothing - row is filtered out
        Else
            For colNumber = 1 To .UsedRange.Columns.Count
                ' Need to check the Columns property of the Worksheet
                ' to ensure we get the entire column
                If (.Columns(colNumber).Hidden) Then
                    ' do something with rowIndex.Cells(1, colNumber)
                Else
                    ' do something else with rowIndex.Cells(1, colNumber)
                End If
            Next colNumber
        End If
    Next rowIndex
End With