vba - 根据单元格的值隐藏 Excel 中的多列

Question

我试图根据某个单元格中的值将列隐藏在工作表中。该值的范围可以从 1 到 30，并且为每个可能的选项隐藏不同的列。我有以下代码，但它似乎并不总是有效，有时它只隐藏范围内的一些列，但不是全部(就像如果 5 位于单元格 AA1 中，那么有时 AN:AR 会隐藏并且 AS:FC 不会被隐藏)。如果有人有任何想法，我将不胜感激。谢谢!

我有一个示例工作簿: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

Answer 1

似乎 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

尽管从问题中并不清楚隐藏/显示的逻辑到底是什么。