SHow Hide COlumns based on cell value

  • Thread starter Thread starter Walt H
  • Start date Start date
W

Walt H

Hello! I want to put validation on a cell call it cell C1 and I will require
the user to select a number between 1 and 6. Whenever the end user changes
the value in cell c1 I want the respective number of columns between column C
and H to be shown. Therefore, if the user puts a 1 in cell C1 I want column
"C" visible and columns "D"through "H" hidden. If the number is 2, I want
columns "C"&"D" visible and "E" through "H" hidden, and so on. Can anyone
help?
Thanks....
 
Put this in the worksheet code module of the sheet you want to hide the
columns. Right click the sheet tab and click on View Code in the drop down
menu. When the VB Editor opens, copy and paste the code below into the code
window.

Private Sub Worksheet_Change(ByVal Target As Range)
Columns("D:H").Hidden = True
If Target = Range("C1") Then
Select Case Target
Case Is = 1
Columns("C").Hidden = False
Case Is = 2
Columns("C:D").Hidden = False
Case Is = 3
Columns("C:E").Hidden = False
Case Is = 4
Columns("C:F").Hidden = False
Case Is = 5
Columns("C:G").Hidden = False
Case Is = 6
Columns("C:H").Hidden = False
End Select
End If
End Sub

Since you want to use C1 as the Target cell, then column C must remain
visible. If you want to use a cell in a column other than C:H then you could
change the line

Columns("D:H").Hidden = True

To:

Columns("C:H").Hidden = True

And all six columns would be hidden until a number is put into the Target
Range
which would also have to be changed in the code line If Target = Range ?
 
Thank you very much for your help. Take care.

JLGWhiz said:
Put this in the worksheet code module of the sheet you want to hide the
columns. Right click the sheet tab and click on View Code in the drop down
menu. When the VB Editor opens, copy and paste the code below into the code
window.

Private Sub Worksheet_Change(ByVal Target As Range)
Columns("D:H").Hidden = True
If Target = Range("C1") Then
Select Case Target
Case Is = 1
Columns("C").Hidden = False
Case Is = 2
Columns("C:D").Hidden = False
Case Is = 3
Columns("C:E").Hidden = False
Case Is = 4
Columns("C:F").Hidden = False
Case Is = 5
Columns("C:G").Hidden = False
Case Is = 6
Columns("C:H").Hidden = False
End Select
End If
End Sub

Since you want to use C1 as the Target cell, then column C must remain
visible. If you want to use a cell in a column other than C:H then you could
change the line

Columns("D:H").Hidden = True

To:

Columns("C:H").Hidden = True

And all six columns would be hidden until a number is put into the Target
Range
which would also have to be changed in the code line If Target = Range ?
 
Sorry for the bother, however, The columns revert to hidden if any other cell
in the worksheet happens to have a value entered into it subsequent to
changing the value in c1/b1. I see where you define the variable but this is
all beyond me so I am asking for a little more help whenever you have a
chance...Thanks
In other words, lets say the target is B1, so I enter "2" in b1 and the
first two columns in the case statement become unhidden. Then, suppose I type
the number "8" in cell b4, the macro then re-hides the two columns that were
shown. I am thinking this is happening because there was a change in the
worksheet where I pasted the code....Thanks....
 
The code can be written two ways. 1) Start with the columns hidden, then
unhide the ones you want to see, or 2) have all columns visible, then hide
the ones you don't want to see.

You have the first option, so any change other than entering 1-6 in C1 or B1
will hide the columns. But, I have modified the code so that if will not
hide any columns unless you make the change in cell B1. Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("B1") Then
Columns("D:H").Hidden = True
Select Case Target
Case Is = 1
Columns("C").Hidden = False
Case Is = 2
Columns("C:D").Hidden = False
Case Is = 3
Columns("C:E").Hidden = False
Case Is = 4
Columns("C:F").Hidden = False
Case Is = 5
Columns("C:G").Hidden = False
Case Is = 6
Columns("C:H").Hidden = False
End Select
End If
End Sub
 
Back
Top