vba 如何确定 Excel 范围是否隐藏？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28074975/
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
How do I determine if an Excel range is hidden?
提问by dimitris
In my code I include a boolean variable in which I want to assign the value of a range's hidden property. i.e. if the range is hidden, the variable should have the value true, and vice versa.
在我的代码中，我包含一个布尔变量，我想在其中分配范围隐藏属性的值。即，如果范围是隐藏的，则变量的值应为真，反之亦然。
While running the code I get a '1004' Run-time error - Unable to get the Hidden property of the Range class. By this I assume that Hidden property in this case is write-only (correct me if I'm wrong).
运行代码时，我收到“1004”运行时错误 - 无法获取 Range 类的 Hidden 属性。通过这个，我假设在这种情况下 Hidden 属性是只写的（如果我错了，请纠正我）。
Is there a way to determine (in my code, not by watching) whether a range/cell is hidden or not?
有没有办法确定（在我的代码中，而不是通过观察）范围/单元格是否隐藏？
I have a class named "minas" and with this sub I am trying to create a collection of minas based on some criteria.
我有一个名为“minas”的类，我正在尝试根据某些标准创建一个 minas 集合。
Public mines As Collection
Sub existing_months()
Set mines = New Collection
Dim min As minas
Dim str As String
Dim x As Range
Dim y As Boolean
For i = 1 To 12
Set min = New minas
Set x = Range("A1:A500").Find(i, LookIn:=xlValues, LookAt:=xlWhole)
If x Is Nothing Then GoTo next_iteration:
y = x.Hidden 'does not get the property
Call min.initialize(x, y)
str = min.minas & "/" & min.etos
mines.Add min, str
Debug.Print min.ref_range.Address & " " & min.end_cell
next_iteration:
Next
Set min = Nothing
End Sub
回答by Gary's Student
You can say a cellis hidden if it is located on a hidden row or hidden column.
Then a rangeis hidden if all cells in that range are hidden:
如果单元格位于隐藏行或隐藏列，则可以说它是隐藏的。
然后一个范围，如果在该范围内的所有细胞都隐藏隐藏：
Public Function IsHidden(rIn As Range) As Boolean
Dim r As Range
IsHidden = True
For Each r In rIn
If Not r.EntireRow.Hidden Then
If Not r.EntireColumn.Hidden Then
IsHidden = False
Exit Function
End If
End If
Next r
End Function
回答by Chrismas007
According to a quick Google search, Range.Findwill not find the data if the cell is hidden if you use LookIn:=xlValues. I tested this with "Test" in Cell A6and hid the row. This code returned Nothing:
根据 Google 的快速搜索，Range.Find如果使用LookIn:=xlValues. 我在 Cell 中用“Test”进行了测试A6并隐藏了该行。此代码返回Nothing：
Sub TestIt()
Dim x As Range
Set x = Range("A1:A7").Find("Test", , xlValues, xlWhole)
If x Is Nothing Then
MsgBox "Nothing"
Else
If x.EntireRow.Hidden = True Then
MsgBox x.Address & " is Hidden"
Else
MsgBox x.Address & " is Visible"
End If
End If
End Sub
Instead you need to use LookIn:=xlFormulas:
相反，您需要使用LookIn:=xlFormulas：
Sub TestIt()
Dim x As Range
Set x = Range("A1:A7").Find("Test", , xlFormulas, xlWhole)
If x Is Nothing Then
MsgBox "Nothing"
Else
If x.EntireRow.Hidden = True Then
MsgBox x.Address & " is Hidden"
Else
MsgBox x.Address & " is Visible"
End If
End If
End Sub
Then you can use either:
然后你可以使用：
y = x.EntireRow.Hidden
or
或者
y = x.EntireColumn.Hidden
to get your Boolean (True if the cell is hidden and False if the cell is visible)
获取您的布尔值（如果单元格隐藏，则为 True，如果单元格可见，则为 False）
回答by user3561813
Do you need to determine if the entire column is hidden? Individual cells can not be hidden. (Unless, of course, you're referring to the HiddenFormula property). If so, the following code should work:
您是否需要确定整个列是否隐藏？不能隐藏单个单元格。（当然，除非您指的是 HiddenFormula 属性）。如果是这样，以下代码应该可以工作：
y = x.entirecolumn.Hidden 'does not get the property
Let me know if this works
让我知道这个是否奏效
