vba 根据 Excel 中的下拉选择显示/隐藏列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19444870/
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
Show/Hide column based on Dropdown selection in Excel
提问by Kashif77
I am trying to show a hidden column based on an option of my dropdown. For a single row it works fine but when I want to extend my Range for 10 rows
我正在尝试根据下拉列表的选项显示隐藏列。对于单行，它工作正常，但是当我想将范围扩展到 10 行时
If Range("$CF$5: $CF$15") = "Others" Then
If Range("$CF$5: $CF$15") = "Others" Then
Tt displays a Runtime error 13.
Tt 显示一个 Runtime error 13.
Below is my code. Thanks for helping me out.
下面是我的代码。谢谢你的协助。
If Range("$CF") = "Others" Then
ActiveSheet.Columns("CG").EntireColumn.Hidden = False
Else
ActiveSheet.Columns("CG").EntireColumn.Hidden = True
End If
回答by Siddharth Rout
You can't compare the value in the range like you are doing it.
您无法像这样做那样比较范围内的值。
If Range("$CF$5: $CF$15") = "Others"
If Range("$CF$5: $CF$15") = "Others"
There are many ways to do the comparison. Looping through the range is the most common way. Below is a another way to check if all the cells in a vertical range have the same value.
有很多方法可以进行比较。循环遍历范围是最常见的方式。下面是另一种检查垂直范围内的所有单元格是否具有相同值的方法。
Is this what you are trying?
这是你正在尝试的吗？
Sub Sample()
Dim ws As Worksheet
Dim rng As Range
'~~> Set your worksheet here
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
'~~> Set your range here
Set rng = .Range("CF5:CF15")
'~~> Check if any cell in the range have "Others"
If Application.WorksheetFunction.CountIf(rng, "Others") = _
rng.Rows.Count Then
.Columns("CG").EntireColumn.Hidden = False
Else
.Columns("CG").EntireColumn.Hidden = True
End If
End With
End Sub
EDIT:
编辑：
And if you want to Show/Hide the column even if there is one instance of "Others` then also you don't need a loop. See this
如果你想显示/隐藏列，即使有一个 "Others` 实例，你也不需要循环。看这个
Sub Sample()
Dim ws As Worksheet
Dim rng As Range
'~~> Set your worksheet here
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
'~~> Set your range here
Set rng = .Range("CF5:CF15")
'~~> Check if all the cells in the range have "Others"
If Application.WorksheetFunction.CountIf(rng, "Others") > 0 Then
.Columns("CG").EntireColumn.Hidden = False
Else
.Columns("CG").EntireColumn.Hidden = True
End If
End With
End Sub
回答by mattboy
How about this? This assumes that if a single cell in the range is set to "Others" that the CG column will be shown, and if none them are, CG will be hidden. Not sure if that's what you're really after?
这个怎么样？这假设如果范围中的单个单元格设置为“其他”，则将显示 CG 列，如果没有，则将隐藏 CG。不确定这是否是您真正想要的？
Dim cell As Range
For Each cell In Range("$CF:$CF")
If cell = "Others" Then
ActiveSheet.Columns("CG").EntireColumn.Hidden = False
Exit For
Else
ActiveSheet.Columns("CG").EntireColumn.Hidden = True
End If
Next cell
