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屋！