I have a specific question and hopefully one of you can provide me with the correct code - I can't write VBA on my own though and I haven't found the right code to tweak yet.
Problem (see attached file): I have a table (C12:I18) and I want to show certain columns based on a choice in one cell (B2). The matrix (C4:I7) states whether a column should be visible or not. One (1) means it is visible and zero (0) means it should be hidden. When I chosse 'All' in B2 then all columns are shown. When I choose 'Employment' then columns E and F should hide.
I need flexibility in terms of adding more columns to the table and certainly my choices will increase (so the matrix will increase).
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
I need flexibility in terms of adding more columns to the table and certainly my choices will increase (so the matrix will increase).
According to your attachment as a VBA starter :
PHP Code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim V, A$ If Target.Address <> "$B$2" Then Exit Sub Application.ScreenUpdating = False [A4].CurrentRegion.EntireColumn.Hidden = False V = Application.Match(Target.Value2, [A4].CurrentRegion.Columns(2), 0) If IsNumeric(V) Then A = Range(Cells(3 + V, 3), Cells(3 + V, 2).End(xlToRight)).Address V = Filter(Evaluate("IF(" & A & "=0,ADDRESS(1,COLUMN(" & A & ")))"), False, False) If UBound(V) > -1 Then Range(Join(V, ",")).EntireColumn.Hidden = True End If Application.ScreenUpdating = True End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Bookmarks