VBA：如何忽略范围内的隐藏行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21892206/
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
VBA: How to ignore hidden rows in range?
提问by thedeepfield
I am trying to do a count of all rows that are highlighted and are visible (not hidden). My count formula works but it is still counting hidden rows that also happen to be hidden. How can I count only highlighted and visible rows?
我正在尝试对所有突出显示且可见（未隐藏）的行进行计数。我的计数公式有效，但它仍在计算碰巧也被隐藏的隐藏行。如何只计算突出显示和可见的行？
'This function will count how many cells in a given range for a given color and are visible
Function COUNTCELLCOLORSIF(CellRange As Range) As Long
Dim rngCell
Application.Volatile
For Each rngCell In CellRange
If rngCell.Interior.ColorIndex = "36" and rngCell.visible Then
COUNTCELLCOLORSIF = COUNTCELLCOLORSIF + 1
End If
Next rngCell
End Function
回答by nutsch
Use specialcells(xlcelltypevisible)
用 specialcells(xlcelltypevisible)
Function COUNTCELLCOLORSIF(CellRange As Range) As Long
Dim rngCell
Application.Volatile
For Each rngCell In CellRange.specialcells(xlcelltypevisible)
If rngCell.Interior.ColorIndex = "36" Then
COUNTCELLCOLORSIF = COUNTCELLCOLORSIF + 1
End If
Next rngCell
End Function
回答by Makah
Try something like this:
尝试这样的事情：
Function COUNTCELLCOLORSIF(CellRange As Range) As Long
Dim rngCell, visibleCells
Application.Volatile
visibleCells = CellRange.SpecialCells(xlCellTypeVisible)
For Each rngCell In visibleCells
If rngCell.Interior.ColorIndex = "36" and rngCell.visible Then
COUNTCELLCOLORSIF = COUNTCELLCOLORSIF + 1
End If
Next rngCell
End Function
