Auto Hiding Columns

  • Thread starter Thread starter Fester
  • Start date Start date
F

Fester

I want to auto hide a specific number of columns depending on a
validation list selection.

IE, user selects a shop ID and columns D, E, F, G, are hidden because
there are 3 people in the shop.

I created a button to accomplish this, but I want to automate as much
as possible for ease of use.

Is this possible?

Fester
 
You could use event code to hide the columns. For example, with a data
validation list in cell A2 (with numbers 1-9):

'===============================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Target.Value > 0 Then
Range(Cells(1, 1), Cells(1, Target.Value)) _
.EntireColumn.Hidden = False
Range(Cells(1, Target.Value + 1), Cells(1, 10)) _
.EntireColumn.Hidden = True
End If
End If
End Sub
'===============================

To use this code, right-click on the sheet tab, and choose View Code.
Copy and paste the code onto the sheet's module, where the cursor is
blinking.
 
OK, I'm not getting it.

The first two columns are frozen, the next columns (C-R) contain the
person's name, and a # sign (alternating).

Depending on the value in the cell (A2 in this case), I want to hide
specific columns.

If the value is 3, then columns I thru R are hidden.
If the value is 6, then colums O thru R are hidden.

Fester
 
In said:
OK, I'm not getting it.
Click to expand...
The first two columns are frozen, the next
columns (C-R) contain the person's name,
and a # sign (alternating).
Click to expand...
Depending on the value in the cell (A2 in
this case), I want to hide specific columns.
Click to expand...
If the value is 3, then columns I thru R are hidden.
If the value is 6, then colums O thru R are hidden.
Click to expand...

Try this

Again, right-click on the sheet tab, choose View Code, and copy
and paste the following code onto the sheet's module.

--

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Target.Value = 3 Then
ActiveCell.Offset(0, 7).Range("A1:J1") _
.EntireColumn.Hidden = True
ElseIf Target.Value = 6 Then
ActiveCell.Offset(0, 13).Range("A1:D1") _
.EntireColumn.Hidden = True
Else
ActiveCell.Offset(0, 6).Range("A1:P1") _
.EntireColumn.Hidden = False
End If
End If
End Sub

--

You can add additional elseif's if needed. The final
else (rather obviously) unhides the hidden columns.

Hope this is what you were looking for.

Wolf

--
*****************************************
Dolor ad tempus est.
Sanatur vulni.
Cicatrices amantur a scortillis.
*****************************************
Life is too short to suffer fools gladly.
*****************************************
You can have Freedom or Peace,
Don't EVER count on having both.
 
Back
Top