Excel VBA 过滤/可见单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14173445/
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
Excel VBA Filtered/Visible Cell
提问by babsdoc
Suppose I have 10 rows of data. I apply some filter to the data and row numbers 7 and 9 are filteredORvisible.
假设我有 10 行数据。我对数据应用了一些过滤器，行号 7 和 9 被过滤或可见。
I want to loop through the data (10 Rows) and output the string "Is Visible"in a blank column (suppose Column C), onlyfor the Visible rows (i.e. Range C7 and Range C9).
我想遍历数据（10 行）并在空白列（假设列 C）中输出字符串“可见”，仅适用于可见行（即范围 C7 和范围 C9）。
回答by whytheq
Choose some of the first 10 rows to hide, and then try running this
选择前 10 行中的一些隐藏，然后尝试运行此
Option Explicit
Sub CheckIfVisible()
Dim i As Integer, x As Integer
x = 0
For i = 1 To 10
With Excel.ThisWorkbook.ActiveSheet
If .Rows(i).EntireRow.Hidden Then
Else
.Cells(15 + x, 1) = "Row " & i & "is visible"
x = x + 1
End If
End With
Next i
End Sub
Is this the sort of loop you're looking for?
Maybe you can show us your Loop so we can see where your problem is?
这是您正在寻找的那种循环吗？
也许您可以向我们展示您的 Loop，以便我们了解您的问题出在哪里？
回答by InstantExcel.com
Since I came here looking for this a while back, here's something that may be of use to future googlers.
由于我不久前来到这里寻找这个，这里有一些可能对未来的谷歌员工有用的东西。
If you're doing it per cell/row,you can just access the cells .EntireRow.Hiddenbool directly.
如果您按单元格/行执行此操作，则可以直接访问单元格.EntireRow.Hiddenbool。
In the below example, it's just a ForEach loop through each Cell in the Selection, and reading the property in that cell only, counting/colouring based on .Hidden being True/False.
在下面的示例中，它只是通过选择中的每个单元格的 ForEach 循环，并仅读取该单元格中的属性，根据 .Hidden 为 True/False 进行计数/着色。
Possible pitfalls to beware of..
需要注意的可能陷阱..
In the event you're testing a filtered range, you'll need to select beyond the range by a row, as you may not capture the hidden rows if they fall on the penultimate /ultimate row in the range, selecting the first visible row after the selection avoids this.
如果您正在测试过滤范围，则需要选择超出范围的一行，因为如果隐藏行落在范围内的倒数第二行/最后一行，您可能无法捕获隐藏的行，选择第一个可见行选择后避免了这种情况。
It's going to be extremely inefficient for large ranges ( 10,000 + rows )
对于大范围（10,000 + 行）来说，这将是非常低效的
CopyPasta if you want to test
CopyPasta 如果你想测试
Sub HowManyHiddenCells()
Dim MyCell, MyRange As Range
Dim CellCountAll, CellCountVisible, CellCountHidden As Integer
Set MyRange = Selection
For Each MyCell In MyRange
':: IGNORE EMPTY ::
If Len(MyCell.text) > 0 Then
If MyCell.EntireRow.Hidden Then
MyCell.Interior.Color = RGB(255, 220, 200)
':: Count of hidden cells in range
CellCountHidden = CellCountHidden + 1
':: Do Column C Text! ::
MyCell.Offset(0, 2).FormulaR1C1 = "I was hidden! "
End If
If MyCell.EntireRow.Hidden = False Then
MyCell.Interior.Color = RGB(200, 255, 180)
':: Count of visible cells in range
CellCountVisible = CellCountVisible + 1
End If
':: Count of all cells in range
CellCountAll = CellCountAll + 1
End If
Next MyCell
MsgBox "Cells total " & CellCountAll & vbNewLine & "Hidden : " & CellCountHidden & vbNewLine & "Visible : " & CellCountVisible, vbOKOnly + vbInformation, "Count of hidden vs visible"
End Sub
Exmaple of script in action - on a filtered range, highlighting the hidden in red
回答by Ralf
Inspired by @whytheq I came up with this, so it loops through all visible rows in selection:
受到@whytheq 的启发，我想出了这个，所以它会遍历selection 中的所有可见行：
Sub Loop_through_selected_rows()
Dim rng As Range: Set rng = ActiveWindow.RangeSelection
Dim i As Integer
For i = 0 To rng.Rows.Count - 1
If Cells(rng.Row + i, 1).EntireRow.Hidden Then
Else
Cells(rng.Row + i, 1).Range("A1:E1").Select 'Set Range within row to your needs
' Do something here
End If
Next
End Sub
