Hide Columns On Condition

[Dominique Feteau]

I think I might have posed this question once before, but now i'm asking it
a bit differently. I have a table with column headers in D33. A2 of this
sheet references the contents of a cell in another sheet that contains a
data validation list that references these column headers. What I would
like to do is a little difficult. I need a macro that will look in D33
and hide those columns that dont match in A2. So would would happen is that
anytime A2 changes, the corresponding column will be the only one (or two)
visible.

Any ideas?

 

[Claud Balls]

How about this?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
For Each c In Sheets("sheet1").Range("D33")
If c <> Sheets("sheet1").Range("A2") Then
c.Columns.ColumnWidth = 0
Else
c.Columns.AutoFit
End If
Next
End Sub

 

[Dominique Feteau]

How do I use this code??

 

[Claud Balls]

Open the worksheet you want to use, go to Tools>Macros>Visual Basic
Editor
Double click ThisWorkbook, and paste the code in. The macro will run
when ever a cell is changed. Also, delete the extra space on the second
line so the end of the first line looks like this:
(ByVal Sh As Object, ByVal Target As Range)

 

[Dominique Feteau]

Thanks Claud. One other question. Can I have this code work over a number
of pages? I was tinkering with the code a bit and I got it to work on the
activesheet, but it doesnt do it automatically. So what would happen is
that if on the "main" page B3 changed, the code would recalculate over
sheets January through December.

 

[Claud Balls]

Try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range) 'This should be one line

If ActiveSheet.Name = "Main" Then
If Not Intersect(Target, Range("B3")) Is Nothing Then
For Each c In Sheets("Sheet1").Range("D33")
If c <> Sheets("sheet1").Range("A2") Then
c.Columns.ColumnWidth = 0
Else
c.Columns.AutoFit
End If
Next
End If
End If
End Sub

 

[Claud Balls]

Sorry, forgot to put in the part about looping through months

If ActiveSheet.Name = "Main" Then
If Not Intersect(Target, Range("B3")) Is Nothing Then
For Each wsh In Sheets(Array _("Jan", "Feb", "Mar", "Apr", "May", _
"Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"))
For Each c In wsh.Range("D33")
If c <> Sheets("sheet1").Range("A2") Then
c.Columns.ColumnWidth = 0
Else
c.Columns.AutoFit
End If
Next
Next
End If
End If
End Sub