Show columns based on cell value

[elmacay]

Hi,

I have 50 columns with cell headings containing 20 different departments. I want to show only the columns based on a cell containing the department name (data validation). Any ideas on how to do this?

Cheers, Elmacay

 

[hippiehacker]

don't think without vba is possible to hide/unhide the columns based on the cell value are the columns fixed per department?

 

[Firefly2012]

Please can you show how your data is represented in these columns (ie post a sample)

 

[elmacay]

Data validation cell (A1) Personnel, Logistics, Facilities

Column.............................A.................B...............C.................D................E...............F.............G...............Z
Column header (row 3).....Personnel......Personnel.....Logistics......Personnel......Logistics.....Facilities....Logistics.......etc.

Macro in words:

If cells in range A3:Z3 contain the value in cell A1 (e.g. "Personnel"), then show only those columns (e.g. A, B and D), and hide all other columns (in this range A:Z).

 

[Firefly2012]

Hi

Right-click the sheet name at the bottom of Excel and choose "View Code". Paste the following in to the code window which will open. You will need to create a named range from your column 3 headers (so select the headers and eg B3:Z3 and type Headers - and press enter - in the text box immediately above the Select All button in Excel).

Please note that you can't hide the A column using this (well, you could, but you wouldn't be able to use the validation cell to unhide it again)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rHdr As Range, rHdrs As Range
Dim strFirstAddr As String
If Target.Address <> "$A$1" Then Exit Sub  'this address is your validation address where you select the header
Set rHdr = Range("Headers").Find(Target.Value, LookIn:=xlFormulas)  'assumes your header range is named "Headers"
If Not rHdr Is Nothing Then
    strFirstAddr = rHdr.Address
    Set rHdrs = rHdr
    Do
        Set rHdrs = Application.Union(rHdrs, rHdr)
        Set rHdr = Range("Headers").FindNext(rHdr)
    Loop Until rHdr.Address = strFirstAddr
    Range("Headers").EntireColumn.Hidden = True
    rHdrs.EntireColumn.Hidden = False
End If

End Sub

 

[Peter_SSs]

Data validation cell (A1) Personnel, Logistics, Facilities

Column.............................A.................B...............C.................D................E...............F.............G...............Z
Column header (row 3).....Personnel......Personnel.....Logistics......Personnel......Logistics.....Facilities....Logistics.......etc.

Macro in words:

If cells in range A3:Z3 contain the value in cell A1 (e.g. "Personnel"), then show only those columns (e.g. A, B and D), and hide all other columns (in this range A:Z).

Seems strange that the Data Validation is in A1 and so is one of the headers (Personnel) in column A. So if A1 is set to Logistics, column A would be hidden because its heading didn't match. However, that also means A1 is hidden which would make it hard to use the Data Validation again.

So I'm actually assuming the Data Validation is in A1 and the headings are in B3:AA3

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & change/delete the value in cell A1.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim cel As Range, Headers As Range
  Dim s As String
  
  Set Headers = Range("B3:AA3")
  If Target.Address = "$A$1" Then
    s = Target.Value
    Application.ScreenUpdating = False
    If s = "" Then
      Headers.EntireColumn.Hidden = False
    Else
      For Each cel In Headers
        cel.EntireColumn.Hidden = Not cel.Value = s
      Next cel
    End If
    Application.ScreenUpdating = True
  End If
End Sub

 

[elmacay]

Thanks guys, works like a charm!

And yes, I can see the problem hiding column A with a value in A1. My bad.

 

[skyrockettes2]

Hi,

I have a similar problem and was able to use the above code. However, the macro only works if in A1 (data validation) I type the name of the department. I actually want to link this A1 to a Combobox that I created in another sheet (called Start info, reference cell D5). If I say that A1='Start info'$D$5, and select a different option in the Combobox, the macro doesn't recognize this as a change and the macro doesn't run. How do I need to amend the code?

My worksheet to which the macro should apply is called "DV". I name ranged B3:CF3 as Headers. The worksheet with the Combobox is "Start info" and has reference cell D5.

I hope you can help out, thanks!

 

[Peter_SSs]

Hi,

I have a similar problem and was able to use the above code. However, the macro only works if in A1 (data validation) I type the name of the department. I actually want to link this A1 to a Combobox that I created in another sheet (called Start info, reference cell D5). If I say that A1='Start info'$D$5, and select a different option in the Combobox, the macro doesn't recognize this as a change and the macro doesn't run. How do I need to amend the code?

My worksheet to which the macro should apply is called "DV". I name ranged B3:CF3 as Headers. The worksheet with the Combobox is "Start info" and has reference cell D5.

I hope you can help out, thanks!

The Worksheet_Change event is not triggered by a formula result changing. Since you have a formula in A1 on the DV sheet, that's the reason nothing is happening.
The 'event' that is triggering any action in your case is happening in the 'Start info' sheet so that is where we need to be working.
However, the Worksheet_Change event is also not triggered by a cell (like your cell D5 in that sheet) change as a result of being linked to a ConboBox.
The good news is that a ComboBox itself has a change event linked to it so we should be able to use that.
So try this ..

1. Remove any Worksheet_Change event code from the 'DV' sheet's module.

2. Paste the code below into the 'Start info' sheet's module.
- Make sure you edit the code (2 places) if your ComboBox is not called ComboBox1
- If you don't want the code to take to the 'DV' sheet after hiding/showing the columns, then just remove the second last line of the code.

Private Sub ComboBox1_Change()
  Dim cel As Range, Hdrs As Range
  Dim s As String
  
  Set Hdrs = Sheets("DV").Range("Headers")
  s = ComboBox1.Value
  If s = "" Then
    Hdrs.EntireColumn.Hidden = False
  Else
    For Each cel In Hdrs
      cel.EntireColumn.Hidden = Not cel.Value = s
    Next cel
  End If
  Hdrs.Parent.Activate
End Sub

 

[skyrockettes2]

Thank you sooo much! It works perfectly