vba SpecialCells(xlCellTypeVisible) 还包括隐藏/过滤的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42351729/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SpecialCells(xlCellTypeVisible) also includes hidden/filtered cells
提问by BruceWayne
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.
我有一个正在使用过滤器的数据集。我只想计算 N 列中可见的总值。数据从第 2 行开始，到第 2047 行结束。
I saw this threadbut 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.
使用我当前的过滤器，我的标题行 (1) 是可见的，行 901 到 937 也是可见的。所以，我想对 N901:N937 求和。
However, the rngkeeps getting set to $N$2:$N$2047. I expected it to be $N$901:$N$937.
但是，rng不断设置为$N$2:$N$2047。我期望它是$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 endrow correctly, but not the start row.
使用提供给我上面链接的线程的函数，我得到了一个范围$N$2:$N$937......所以至少，我正确地得到了结束行，但不是开始行。
But!if I type Range("N2:N2047").SpecialCells(xlCellTypeVisible).Selectin the Immediate Window, outside of a macro, it correctly selects just the visible cells. And one step further, doing ?Range("N2:N2047").SpecialCells(xlCellTypeVisible).addresscorrectly returns $N$901:$N$937.
但！如果我Range("N2:N2047").SpecialCells(xlCellTypeVisible).Select在宏之外的立即窗口中键入，它会正确选择可见单元格。更进一步，?Range("N2:N2047").SpecialCells(xlCellTypeVisible).address正确执行返回$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?
编辑：我刚刚发现这样做=SUBTOTAL(9,N1:N2047)只会对可见单元格求和，所以我正在使用它。但我的问题仍然存在 - 为什么SpecialCells(xlCellTypeVisible)在宏中不能正常工作？
回答by Shai Rado
Try setting your rngwith the line below:
尝试rng使用以下行设置您的：
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:
稍后使用您的调试行Debug.Print rng.Address，我在即时窗口中得到以下范围：
$N1:$N7
