vba 根据单元格的值在Excel中隐藏多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27477872/
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
Hiding multiple columns in Excel based on the value of a cell
提问by luna2000
I am trying to get columns to hide in a worksheet based on the value in a certain cell. This value could range from 1 to 30, and different columns are hidden for each possible option. I have the following code, but it doesn't seem to work all the time, and then sometimes it only hides some of the columns in the range, but not all (like if 5 was in cell AA1, then sometimes AN:AR will hide and AS:FC will not be hidden). If anyone has any ideas, I would appreciate the help. Thanks!
我试图根据某个单元格中的值将列隐藏在工作表中。该值的范围可以从 1 到 30，并且每个可能的选项都隐藏了不同的列。我有以下代码，但它似乎并没有一直工作，然后有时它只隐藏范围内的一些列，但不是全部（就像 5 在单元格 AA1 中，那么有时 AN:AR 会隐藏并且 AS:FC 不会被隐藏）。如果有人有任何想法，我将不胜感激。谢谢！
I have a sample workbook at: https://drive.google.com/file/d/0B8qPItN2DU0BZ3B0LW1XUS1BMFk/view?usp=sharing
我有一个示例工作簿：https: //drive.google.com/file/d/0B8qPItN2DU0BZ3B0LW1XUS1BMFk/view?usp=sharing
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("AA1").Value = 1 Then
Columns("O:FC").EntireColumn.Hidden = True
Else
Columns("O:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 2 Then
Columns("T:FC").EntireColumn.Hidden = True
Else
Columns("T:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 3 Then
Columns("Y:FC").EntireColumn.Hidden = True
Else
Columns("Y:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 4 Then
Columns("AD:FC").EntireColumn.Hidden = True
Else
Columns("AD:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 5 Then
Columns("AI:FC").EntireColumn.Hidden = True
Else
Columns("AI:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 6 Then
Columns("AN:FC").EntireColumn.Hidden = True
Else
Columns("AN:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 7 Then
Columns("AS:FC").EntireColumn.Hidden = True
Else
Columns("AS:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 8 Then
Columns("AX:FC").EntireColumn.Hidden = True
Else
Columns("AX:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 9 Then
Columns("BC:FC").EntireColumn.Hidden = True
Else
Columns("BC:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 10 Then
Columns("BH:FC").EntireColumn.Hidden = True
Else
Columns("BH:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 11 Then
Columns("BM:FC").EntireColumn.Hidden = True
Else
Columns("BM:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 12 Then
Columns("BR:FC").EntireColumn.Hidden = True
Else
Columns("BR:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 13 Then
Columns("BW:FC").EntireColumn.Hidden = True
Else
Columns("BW:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 14 Then
Columns("CB:FC").EntireColumn.Hidden = True
Else
Columns("CB:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 15 Then
Columns("CG:FC").EntireColumn.Hidden = True
Else
Columns("CG:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 16 Then
Columns("CL:FC").EntireColumn.Hidden = True
Else
Columns("CL:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 17 Then
Columns("CQ:FC").EntireColumn.Hidden = True
Else
Columns("CQ:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 18 Then
Columns("CV:FC").EntireColumn.Hidden = True
Else
Columns("CV:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 19 Then
Columns("DA:FC").EntireColumn.Hidden = True
Else
Columns("DA:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 20 Then
Columns("DF:FC").EntireColumn.Hidden = True
Else
Columns("DF:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 21 Then
Columns("DK:FC").EntireColumn.Hidden = True
Else
Columns("DK:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 22 Then
Columns("DP:FC").EntireColumn.Hidden = True
Else
Columns("DP:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 23 Then
Columns("DU:FC").EntireColumn.Hidden = True
Else
Columns("DU:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 24 Then
Columns("DZ:FC").EntireColumn.Hidden = True
Else
Columns("DZ:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 25 Then
Columns("EE:FC").EntireColumn.Hidden = True
Else
Columns("EE:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 26 Then
Columns("EJ:FC").EntireColumn.Hidden = True
Else
Columns("EJ:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 27 Then
Columns("EO:FC").EntireColumn.Hidden = True
Else
Columns("EO:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 28 Then
Columns("ET:FC").EntireColumn.Hidden = True
Else
Columns("ET:FC").EntireColumn.Hidden = False
End If
If Range("AA1").Value = 29 Then
Columns("EY:FC").EntireColumn.Hidden = True
Else
Columns("EY:FC").EntireColumn.Hidden = False
End If
End Sub
采纳答案by Tim Williams
Seems like there's a pattern of 5 columns per integer increase in the value in AA1, so:
似乎 AA1 中的值每增加一个整数就有 5 列的模式，所以：
Dim v, sht
Set sht = ActiveSheet
v = sht.Range("AA1").Value
sht.Columns("O:FC").EntireColumn.Hidden = False
If v >= 1 And v <= 30 Then
sht.Range(sht.Cells(1, 10+(5*v)), _
sht.Range("FC1")).EntireColumn.Hidden = True
End If
Although it's not particularly clear from the question exactly what the logic for hiding/showing is.
尽管从问题中并不清楚隐藏/显示的逻辑到底是什么。
回答by Greg the Incredulous
You are hiding a range of columns, and as your code is executed you are hiding, then re-displaying a lot of the columns.
您正在隐藏一系列列，当您执行代码时，您正在隐藏，然后重新显示许多列。
You would be better to firstly display all columns in the largest range (O:FC) in your example, and then only hide the ones that match your value. So you could change the if else constructs to be a select case like this:
您最好首先在示例中显示最大范围 (O:FC) 中的所有列，然后仅隐藏与您的值匹配的列。因此，您可以将 if else 构造更改为这样的选择案例：
Columns("O:FC").EntireColumn.Hidden = False
select case Range("AA1").Value
case 1:
Columns("O:FC").EntireColumn.Hidden = True
case 2:
Columns("T:FC").EntireColumn.Hidden = True
' Add extra cases for each possible value
end select
