Excel VBA - 如何引用单元格的可见内容而不是其公式？

I'm trying to loop through a particular range in my Excel spreadsheet(("B13:B65"), to be specific) and hide all rows that have an "X" in them. Something like this:

我试图在我的Excel电子表格（（“B13：B65”）中循环浏览特定范围，具体而言）并隐藏其中包含“X”的所有行。像这样的东西：

For i = 13 to 65
If Cells(i, 2) = "x" Or "X" Then Rows(i).RowHeight = 0
Next i

The problem is that I'm getting a type mismatch error.

问题是我遇到了类型不匹配错误。

I assume this is happening because all the cells in this range are formulas rather than text strings. For example, the contents of cell B13 are:

我认为这种情况正在发生，因为此范围内的所有单元格都是公式而不是文本字符串。例如，单元格B13的内容是：

='Monthly'!$C$13

I want my code to evaluate the visible output of the cell, not the actual content.

我希望我的代码评估单元格的可见输出，而不是实际内容。

I get the feeling there's a very easy solution here, but I've been searching for a while with no success. I'm a rookie, obviously...

我觉得这里有一个非常简单的解决方案，但我一直在寻找一段时间没有成功。我是菜鸟，显然......

5 个解决方案

#1

Based on this example: https://msdn.microsoft.com/en-us/library/office/ff195193.aspx

基于此示例：https：//msdn.microsoft.com/en-us/library/office/ff195193.aspx

Sub Main()

For Each c in Worksheets("Sheet1").Range("A1:D10")    'Change for your range
 If Lcase(c.Value) = "x" Then 
 '''Rest of your code
 End If 
Next c

end sub

#2

You're getting the error because of the OR. There has to be something that can be evaluated to true or false after the Or. Or "X" won't ever be true or false. You need...

由于OR，您收到错误。在Or之后，必须有一些东西可以被评估为真或假。或者“X”永远不会是真或假。你需要...

If Cells(i, 2) = "x" Or Cells(i, 2) = "X" Then Rows(i).RowHeight = 0

如果Cells（i，2）=“x”或Cells（i，2）=“X”则行（i）.RowHeight = 0

As long as you wanted to use the same code everywhere else.

只要你想在其他地方使用相同的代码。

#3

Use Value property:

使用Value属性：

If Cells(i, 2).Value = "x"

#4

Loop through static range

循环通过静态范围

Dim rng As Range, c As Range
Set rng = Range("B13:B65")

For Each c In rng.Cells
    If UCase(c) = "X" Then
        c.EntireRow.Hidden = True
    End If
Next c

#5

You could use an AutoFilter

您可以使用AutoFilter

Sub HideEm()
Dim rng1 As Range
Set rng1 = ActiveSheet.Range("$B$1:$B$65")
rng1.Parent.AutoFilterMode = False
rng1.AutoFilter Field:=1, Criteria1:="<>x", Operator:=xlOr, Criteria2:="<>X"
End Sub

#1