Hide/Unhide Columns based on Drop-Down Selection

Hello,

I have a few criteria to select from in data validation boxes in column A (Specifically cell A7 thru A10). Data is populated throughout the rest of the worksheet from cell C1:XX1. Is it possible to hide all columns that DO NOT match the word selected in the drop down in cell A9? For example, if I selected "WIN" in the A9 dropdown, I'd like it to hide all columns that do not match this criteria. In this case, when I select WIN from the A9 dropdown, it would hide all columns that do not have the value WIN in cells C9:XX9.

Image attached for reference. Highlighted cells have data validation selections.
Capture1.PNG

Thanks DBY!

Quick question and now I realized I should have stated this earlier. This is the current code I am using. The main distinction is that I would like it to search for values based on the data validation boxes in Column A9. The code below only searches and hides based on what I tell it to do. In short, I want the macro to recognize the text from the data validation box and search from there.

Sub ShowHide()
Dim c As Range

Application.ScreenUpdating = False

For Each c In Range("C9:XX9").Cells
If c.Value <> "WIN" Then
c.EntireColumn.Hidden = True

End If
Next c
End Sub

Hello
My code does run from a value change in cell A9, which I'm assuming is a Data Validation drop down list. This is the 'Target' as referenced in the code (highlighted red) and the search is based on the value selected.

Please Login or Register  to view this content.

This runs from the Worksheet Change Event. Does this help?

Yes it does. I went ahead and changed the code so that it hides all columns that do not meet the criteria. Here's the modified version. Only issue now is that it does not hide the other columns (D,E, and *blue columns*) from the image. This code only hides the columns that have the relevant cell (green columns).

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$9" Then
Application.ScreenUpdating = False
Dim c As Long

For c = 3 To 648 Step 5
If Cells(9, c).Value = Target.Value Then
Cells(9, c).EntireColumn.Hidden = False
Else
Cells(9, c).EntireColumn.Hidden = True
End If
Next c
End If
End Sub

Thanks again for your help thus far.

UPDATE:

I just deleted "Step 5" from your code and it works beautifully! I also pasted and modified this code across cells A7, A8, and A10 so that it works for each of these drop down selections.

Now for the million dollar question...
How do I run a macro that searches for multiple criteria at a time rather than just one?? If you could solve this it would be incredible!!! Here the code as it stands with individual selections hiding columns. Could there be a button that does this same function but matches multiple criteria and skips if the list box filed is blank???

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$7" Then
Application.ScreenUpdating = False
Dim c As Long

For c = 3 To 648
If Cells(7, c).Value = Target.Value Then
Cells(7, c).EntireColumn.Hidden = False
Else
Cells(7, c).EntireColumn.Hidden = True
End If
Next c
End If

If Target.Address = "$A$8" Then
Application.ScreenUpdating = False

For c = 3 To 648
If Cells(8, c).Value = Target.Value Then
Cells(8, c).EntireColumn.Hidden = False
Else
Cells(8, c).EntireColumn.Hidden = True
End If
Next c
End If

If Target.Address = "$A$9" Then
Application.ScreenUpdating = False

For c = 3 To 648
If Cells(9, c).Value = Target.Value Then
Cells(9, c).EntireColumn.Hidden = False
Else
Cells(9, c).EntireColumn.Hidden = True
End If
Next c
End If

If Target.Address = "$A$10" Then
Application.ScreenUpdating = False

For c = 3 To 648
If Cells(10, c).Value = Target.Value Then
Cells(10, c).EntireColumn.Hidden = False
Else
Cells(10, c).EntireColumn.Hidden = True
End If
Next c
End If
End Sub

Thank you so much this is huge progress so far!!

Your above code could be shortened, for example:

Please Login or Register  to view this content.

Could there be a button that does this same function but matches multiple criteria

Where are you going to place your multiple criteria for the code to reference?

I would select my criteria in the A column. In cells A7 through A10. For instance, I would select "New" in A7, leave A8 blank, select "WIN" in A9 and leave A10 blank. The macro would then show columns that only match the criteria selected in these validation cells.

And thanks for the short code tip! I'm a VBA amateur if you did not notice already.

Yes, like a multiple filter. I have to leave my PC just now it's evening here in the UK. I'll give this some thought and get back to you. In the meantime if other forum members offer a solution all's well.

DBY

Thank you Kindly Sir. I will await your response!

Hi
I couldn't get Vicho's code to work properly on my test file, but here is that file with a possible solution. It is difficult to know exactly how this will work out without seeing your actual layout it's a bit of guess work. Whatever is selected in the Blue Criteria cells is shown in the columns with the Yellow validation lists. There's also a command button to clear all and show the columns in the range C7:XX7.

Maybe you can adapt something like this to your actual workbook.

DBY

I couldn't get vichos code to work either. I just loaded your sample excel DBY and it does work but its not exactly what im looking for. This is I knew this would be difficult but now they we are using your example file I can be more clear. You certainly have the right idea, but I'm looking for a button to press AFTER each list box choice is made. So in your blue criteria cells the columns wouldnt hide until you have chosen (or left blank) the criteria in your blue boxes - then you would initiate the macro to search across all your criteria at once.

Right now the code runs after each individual selection. Change your example so that your criterion are different across the four blue cells, then you can search for a column that matches all four criterion or three or two. I hope this makes sense as it may be difficult to explain.
Capture1.PNG

So clicking a "Search" button would yield any columns that match all criterion.

UPDATE:

Attachment 480311

Clicking the search button would yield the following result in the photo. Hope this helps. I am still using DBY's code to be able to filter by individual selection, but an add-on button the searches for all criterion is what I am looking for as well.

Thanks much.

I'm getting an 'Invalid Attachment' error with the attachment. Have you uploaded it as an image?

Yes. Here it is again.Capture1.PNG

Hi
This is my latest take on what you're wanting. This returns all Yellow columns that have an exact match with the Blue search criteria, like for like after clicking Search Button.

DBY....

This is brilliant. I am blown away at your ability to do this. Thank you so much for your help!!!

Im just speechless....

Glad we could help. I've been on the forum a few years and picked up a few ideas along the way but there's still so much to learn. Some of the code I see uploaded here often leaves me stumped. Have a good day.