根据Excel中的下拉列表选项显示/隐藏列 [英] Show/Hide column based on Dropdown selection in Excel

问题描述

我试图根据我的下拉菜单的选项显示一个隐藏的列。对于单排它可以正常工作，但是当我要扩展我的范围10行

如果Range（$ CF $ 5：$ CF $ 15 ）=其他然后

Tt显示一个运行时错误13。 / p>

以下是我的代码。感谢您的帮助。

 如果Range（$ CF $ 5）=其他然后
 ActiveSheet。列（CG）。EntireColumn.Hidden = False 
 Else 
 ActiveSheet.Columns（CG）。EntireColumn.Hidden = True 
如果
  

解决方案

您无法比较您正在做的范围中的值。

如果范围（$ CF $ 5：$ CF $ 15）=其他

有很多方法可以进行比较。通过范围循环是最常见的方式。以下是检查垂直范围内所有单元格是否具有相同值的另一种方法。

这是您正在尝试的？

  Sub Sample（）
 Dim ws As Worksheet 
 Dim rng As Range 
 
'~~>设置你的工作表
设置ws = ThisWorkbook.Sheets（Sheet1）
 
与ws 
'~~>设置你的范围
设置rng = .Range（CF5：CF15）
 
'~~>检查范围内的任何单元格是否有其他
如果Application.WorksheetFunction.CountIf（rng，Others）= _ 
 rng.Rows.Count然后
 .Columns（CG ）.EntireColumn.Hidden = False 
 Else 
 .Columns（CG）。EntireColumn.Hidden = True 
 End If 
 End With 
 End Sub 
  

编辑：

如果你想显示/隐藏列，即使有一个实例Others，那么也不需要循环。看到这个

  Sub Sample（）
 Dim ws As Worksheet 
 Dim rng As Range 
 
'~~>设置您的工作表
设置ws =这个工作簿（Sheet1）
 
用ws 
'~~>设置你的范围
设置rng = .Range（CF5：CF15）
 
'~~>检查范围内的所有单元格是否具有其他
如果Application.WorksheetFunction.CountIf（rng，Others）> 0则
 .Columns（CG）。EntireColumn.Hidden = False 
 Else 
 .Columns（CG）。EntireColumn.Hidden = True 
 End If 
 End 
 End Sub 
  

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

If Range("$CF$5: $CF$15") = "Others" Then

Tt displays a Runtime error 13.

Below is my code. Thanks for helping me out.

If Range("$CF$5") = "Others" Then
    ActiveSheet.Columns("CG").EntireColumn.Hidden = False
Else
    ActiveSheet.Columns("CG").EntireColumn.Hidden = True
End If 

解决方案

You can't compare the value in the range like you are doing it.

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

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

这篇关于根据Excel中的下拉列表选项显示/隐藏列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！