Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.5k views
in Technique[技术] by (71.8m points)

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

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...