Addressing Only Visible Cells when Using the .SpecialCells(xlCellTypeVisible) Method

Question

I wish to perform calculations on only the visible cells of a filtered table range. I am able to set the filtered range using the .SpecialCells(xlCellTypeVisible) method but when I loop through the resulting range, the code seems to be addressing the non-visible cells.

Data: enter image description here

Filtered Data: enter image description here

Code:

    Dim Filter_Range As String
    Dim h As Integer
    Set FilteredRecord = shTable.Range("A2:A7").SpecialCells(xlCellTypeVisible)
    Debug.Print "Filtered count: " & FilteredRecord.Count
    Debug.Print "Filtered address: " & FilteredRecord.Address
    For h = 1 To FilteredRecord.Count
        Debug.Print h & " = " & FilteredRecord(h) & "/address: " & FilteredRecord(h).Address
    Next

Output:

    A2:A7
    Filtered count: 4
    Filtered address: $A$2,$A$4,$A$6:$A$7
    1 = ABC/address: $A$2
    2 = DEF/address: $A$3
    3 = ABC/address: $A$4
    4 = DEF/address: $A$5

So the count and the filtered address range are exactly correct, but the values returned from stepping through filtered range are just the first 4 rows of data, including values that should have been hidden ($A$3 and $A$5). The following is what I expected the results to be:

    1 = ABC/address: $A$2
    2 = ABC/address: $A$4
    3 = XYZ/address: $A$6
    4 = XYZ/address: $A$7

Quite the conundrum. I greatly appreciate any help - thank you!

Answer 1

FilteredRecord(h) is equivalent to FilteredRecord.Item(h). Its result is same as FilteredRecord.Cells(h). For a non continuous range, it is NOT a iteration within the target range.

Please try.

Sub demo()
    Dim Filter_Range As String
    Dim c As Range, h As Long
    Set FilteredRecord = ActiveSheet.Range("A2:A7").SpecialCells(xlCellTypeVisible)
    Debug.Print "Filtered count: " & FilteredRecord.Count
    Debug.Print "Filtered address: " & FilteredRecord.Address
    h = 1
    For Each c In FilteredRecord.Cells
        Debug.Print h & " = " & c.Value & "/address: " & c.Address
        h = h + 1
    Next
End Sub

Answer 2

Well this is interesting - I solved it right after posing my question. I should not have been iterating through the range with an index, I should have used a For Each on the filtered range. The following modified code works perfectly:

    Dim FilteredRecord As Range
    Dim Record As Range
    Dim h As Integer
    Filter_Range = "A2:A7"
    Set FilteredRecord = shTable.Range("A2:A7").SpecialCells(xlCellTypeVisible)
    Debug.Print "Filtered count:" & FilteredRecord.Count
    Debug.Print FilteredRecord.Address
    For Each Record In FilteredRecord.Rows
           Debug.Print Record & "/address: " & Record.Address
    Next

Output is:

Filtered count:4
Filtered address: $A$2,$A$4,$A$6:$A$7
ABC/address: $A$2
ABC/address: $A$4
XYZ/address: $A$6
XYZ/address: $A$7

Answer 3

The explanation of this result, that if a range contains non-contiguous cells, than the resulted Range object has "areas", which are the list of the contiguous ranges.

This can be reached with the Areas property of a range.

Try this

set a=shTable.Range("A2:A7").SpecialCells(xlCellTypeVisible)
For i=1 To a.Areas.Count
  Debug.Print a.Areas(i).Address
Next i