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

 

[Peter_SSs]

Thank you sooo much! It works perfectly

Great news.

 

[mnyankee1]

Peter,

This macro works perfectly and is fantastic! I am new to macros and do have a question about modifying your macro. I have the need to be able to select multiple columns based on different values. IE: column headers equal to 12,17, 37 and 142. Any help will be greatly appreciated!

Randy

 

[Peter_SSs]

Peter,

This macro works perfectly and is fantastic! I am new to macros and do have a question about modifying your macro. I have the need to be able to select multiple columns based on different values. IE: column headers equal to 12,17, 37 and 142. Any help will be greatly appreciated!

Randy

Welcome to the MrExcel board!

I don't understand "need to be able to select multiple columns based on different values. IE: column headers equal to 12,17, 37 and 142.".
Can you spell out in more detail what you have and what you are trying to do?

 

[mnyankee1]

Sure Peter. What I mean is that I need a way to input multiple values in say cell B1 or multiple cells and then have the corresponding columns visible with all others hidden. Hope that makes more sense.

Rany

 

[Peter_SSs]

Sure Peter. What I mean is that I need a way to input multiple values in say cell B1 or multiple cells and then have the corresponding columns visible with all others hidden. Hope that makes more sense.

Rany

So are "12,17 etc" column numbers (that is columns L, Q, etc) or are they headings (in row 1)?

 

[mnyankee1]

They are headings in row 1. I apologize for this being unclear. I should have used text instead of numbers for my column headings.

 

[Peter_SSs]

OK, well I'm using text headers to demo. Try this in a copy of your workbook.

You mentioned putting your list possibly in cell B1. Instead of that, I'm suggesting making the list of the headers you want visible in column B, starting in row 2, with each header in a separate cell.
So, I'm imagining the sheet to look something like this to start with.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L
1	Visible Headers	Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7	Header 8	Header 9
2		data	data	data	data	data	data	data	data	data
3		data	data	data	data	data	data	data	data	data
4		data	data	data	data	data	data	data	data	data
5		data	data	data	data	data	data	data	data	data
6		data	data	data	data	data	data	data	data	data
7
Show or Hide Columns

Then use this Worksheet_Change code

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Headers As Range, cel As Range, VisibleList As Range
  
  If Not Intersect(Target, Columns("B")) Is Nothing Then
    Application.ScreenUpdating = False
    Set Headers = Range("C1:K1")
    Set VisibleList = Range("B1", Range("B" & Rows.Count).End(xlUp))
    If VisibleList.Cells.Count = 1 Then
      Headers.EntireColumn.Hidden = False
    Else
      For Each cel In Headers
        cel.EntireColumn.Hidden = IsError(Application.Match(cel.Value, VisibleList, False))
      Next cel
    Application.ScreenUpdating = True
    End If
  End If
End Sub

So, after entering some values in column B, the sheet might then look like this.

Excel Workbook
	B	D	E	G	I	K	L
1	Visible Headers	Header 2	Header 3	Header 5	Header 7	Header 9
2	Header 7	data	data	data	data	data
3	Header 5	data	data	data	data	data
4		data	data	data	data	data
5	Header 2	data	data	data	data	data
6	Header 9	data	data	data	data	data
7	Header 3
8
Show or Hide Columns

I have made the assumption that you would never want all columns hidden so the code will show all columns if all the headers are in column B or if there are no headers at all listed below B1. If that is not what you want, post back with more details.

 

[mnyankee1]

Peter,

This works perfectly! The only thing I have left to figure out is how to make the column headers a selectable list. My current spreadsheet has 275 columns making it difficult to know what is available to filter on. We could easily want to see dozens of columns at the same time. If there is no easy way to do this then we will work with it the way it is. I am quite impressed at what you have done already so thanks for all of your help!

Randy

 

[Peter_SSs]

The only thing I have left to figure out is how to make the column headers a selectable list.

Try this:

1. Remove all values from B2 down, so that all columns are visible.

2. Select all headers (for my sample that is C1:K1) and give that range the name 'Headers'

3. Select column B and apply data Validation of Allow: List and Source: =Headers

Having done that you could change the previous code slightly to make use of the named range.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim cel As Range, VisibleList As Range
  
  If Not Intersect(Target, Columns("B")) Is Nothing Then
    Application.ScreenUpdating = False
    Set VisibleList = Range("B1", Range("B" & Rows.Count).End(xlUp))
    If VisibleList.Cells.Count = 1 Then
      Range("Headers").EntireColumn.Hidden = False
    Else
      For Each cel In Range("Headers")
        cel.EntireColumn.Hidden = IsError(Application.Match(cel.Value, VisibleList, False))
      Next cel
    Application.ScreenUpdating = True
    End If
  End If
End Sub

 

[DAWINDERBABBAR]

Hello Peter,

Thank you for this macro. This works amazingly fine, however I am trying to move this to next step. what I mean is that I have a sheet with headers like jan2015, Feb 2015....Jan 2016,feb 2016... 2017 . is there a way that I can create two drop down list and then all columns that match either of the headers are visible and rest all hidden. so lest say that I want to compare all 2015 actual with all 2016 actual so I can select "2015 actual" on one drop down and "2016 actual" on other drop down so all these columns will show and I can compare.