excel - SpecialCells(xlCellTypeVisible) also includes hidden/filtered cells

Question

I have a dataset that I'm using a filter on. I simply want to calculate the total values in column N, that are visible. The data starts in row 2, and ends at row 2047.

I saw this thread but it gives me the same type of issue I'm having.

Here's my function:

Function sumVisible() As String
Dim rng As Range
Set rng = Range("N2:N2047").SpecialCells(xlCellTypeVisible)
' Debug.Print "Range: " & rng.Address & ", Sum: " & WorksheetFunction.Sum(rng)
sumVisible = Format(WorksheetFunction.Sum(rng), "$#,###.##")
End Function

With my current filter, my header row (1) is visible, as are rows 901 to 937. So, I want to sum N901:N937.

However, the rng keeps getting set to $N$2:$N$2047. I expected it to be $N$901:$N$937.

Using the function that is given to the thread I linked to above, I get a range of $N$2:$N$937...so at the very least, I'm getting the end row correctly, but not the start row.

But! if I type Range("N2:N2047").SpecialCells(xlCellTypeVisible).Select in the Immediate Window, outside of a macro, it correctly selects just the visible cells. And one step further, doing ?Range("N2:N2047").SpecialCells(xlCellTypeVisible).address correctly returns $N$901:$N$937.

What may be going wrong?

Edit: I just found that doing =SUBTOTAL(9,N1:N2047) will just sum the visible cells, so I'm using that. But my question still stands - why isn't SpecialCells(xlCellTypeVisible) working correctly in the macro?

Answer 1

Try setting your rng with the line below:

Set rng = Range("N2:N" & Cells(Rows.Count, "N").End(xlUp).Row).SpecialCells(xlCellTypeVisible)

Later using your debug line Debug.Print rng.Address, I get the following range in the immediate window:

$N$901:$N$937