Clever Geek Handbook

Hide multiple columns in Excel based on cell value

I am trying to get the columns to hide in a sheet based on the value in a specific cell. This value can range from 1 to 30, and different columns are hidden for each possible option. I have the following code, but it doesn't work all the time, and sometimes it hides only some of the columns in the range, but not all (for example, if 5 was in cell AA1, then sometimes AN: AR will hide and AS: FC will not is hidden). If anyone has any ideas I would appreciate some help. Thank!

I have a sample workbook at: 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
+3


source to share


2 answers


The 5 column pattern seems to be an integer incremented in value in AA1, so:

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


Even though the question of what the hide / show logic is is not quite clear from the question.

+3


source


You hide a range of columns, and as your code runs, you hide and then re-display many columns.

It's better to first display all columns in the largest range (O: FC) in your example, and then hide only those that match your value. Thus, you can modify the if else construct to be a similar example:



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
+3


source






More articles:


All Articles