trying to hide columns depending on the number in a particular cell

**can2c** · 03-15-2010, 11:22 AM

Hi

In a spreadsheet I have columns H to GE (180 columns) highlighted yellow. What I would like to do is hide these columns depending on the value that I put in cell B4. So if I leave that cell blank, then all of the columns show. If I put a 1 in B4, only column H shows. If I put a 2, columns H and I show, and the rest are hidden, and so on, all the way upto 180, in which case all the columns show. Does anyone know how to do this? I'm using Excel 2007.

Thanks

**mdbct** · 03-15-2010, 03:30 PM

Right click on the tab where you would like the event to occur and choose View Code... Paste the following:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Application.ScreenUpdating = False
If Not Intersect(Range("B4"), Target) Is Nothing Then
For i = 7 To 187
    If Cells(1, i).Column - 7 >= Target.Value Then
        Cells(1, i).EntireColumn.Hidden = True
    Else
        Cells(1, i).EntireColumn.Hidden = False
    End If
Next
End If
Application.ScreenUpdating = True
End Sub

**can2c** · 03-15-2010, 05:08 PM

Hi

Thanks for this. It does work but there a couple of issues.

If I put 0 in B4, it hides all columns from G to GE.
If I put 1 in B4, it hides all columns from H to GE.
If I put 2, just column H shows.

Instead, I'd like that:

when I put 0, no columns are hidden
when I put 1, just column H shows
when I put 2, columns H and I show
etc

**mdbct** · 03-15-2010, 05:19 PM

Try this version

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Application.ScreenUpdating = False
If Not Intersect(Range("B4"), Target) Is Nothing Then

If Target = 0 Then
Range("H:GE").EntireColumn.Hidden = False
Exit Sub

Else

For i = 8 To 188
    If Cells(1, i).Column - 8 >= Target.Value Then
        Cells(1, i).EntireColumn.Hidden = True
    Else
        Cells(1, i).EntireColumn.Hidden = False
    End If
Next
End If
End If
Application.ScreenUpdating = True
End Sub