SpecialCells(xlCellTypeVisible)还包括隐藏/过滤的单元格 [英] SpecialCells(xlCellTypeVisible) also includes hidden/filtered cells
问题描述
我有一个使用过滤器的数据集.我只想计算可见的N列中的总值.数据从第2行开始,到2047行结束.
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.
这是我的职能:
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
使用当前的过滤器,我的标题行(1)可见,行901至937也可见.因此,我想对N901:N937求和.
With my current filter, my header row (1) is visible, as are rows 901 to 937. So, I want to sum N901:N937.
但是, rng
一直设置为 $ N $ 2:$ N $ 2047
.我希望它是 $ N $ 901:$ N $ 937
.
However, the rng
keeps getting set to $N$2:$N$2047
. I expected it to be $N$901:$N$937
.
使用赋予我上面链接到的线程的函数,我得到了 $ N $ 2:$ N $ 937
的范围...因此至少,我得到了正确地 end 行,而不是开始行.
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.
但是!如果我在宏窗口之外的立即窗口中键入 Range("N2:N2047").SpecialCells(xlCellTypeVisible).Select
,它将正确选择只是可见的细胞.再进一步,执行?Range("N2:N2047").SpecialCells(xlCellTypeVisible).address
正确返回 $ N $ 901:$ N $ 937
.
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
.
可能出了什么问题?
我刚刚发现做 = SUBTOTAL(9,N1:N2047)
只会对可见单元格求和,所以我正在使用它.但是我的问题仍然存在-为什么 SpecialCells(xlCellTypeVisible)
在宏中不能正常工作?
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?
推荐答案
尝试在下面的行中设置 rng
:
Try setting your rng
with the line below:
Set rng = Range("N2:N" & Cells(Rows.Count, "N").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
稍后使用您的调试行 Debug.Print rng.Address
,我在立即窗口中得到以下范围:
Later using your debug line Debug.Print rng.Address
, I get the following range in the immediate window:
$N$901:$N$937
这篇关于SpecialCells(xlCellTypeVisible)还包括隐藏/过滤的单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!