Macro to hide columns based off List value in a cell

[joles]

Hello everyone - I did find a thread that I think is close to what I'm looking for - but I'm having trouble translating it to look beyond one row's worth of conditionals.

Basically I have a list of properties in the columns for a full set of product categories, and based off a selection in a list dropdown I would like to be able to hide the properties that are not applicable to that selected category.

So in this example, with BRA selected in D8, I would want columns I, J, and K to be hidden... and if SOCKS were selected, only I and K... and so on and so forth, with nothing hidden if D8 is blank. FWIW I did try to install the mini-sheet extension here however I it was not displaying for me even though the add-in shows installed... I'm sure user error but I'm not sure where, so just posting a screenshot!

 

[DanteAmor]

Put this code in the events of your sheet.
SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Now, select a data in cell D8 or delete the data, the columns will be shown and hidden automatically.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim f As Range
  Dim lc As Long, i As Long
  
  If Target.CountLarge > 1 Then Exit Sub
  Application.ScreenUpdating = False
  If Target.Address(0, 0) = "D8" Then
    lc = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
    Range("E1", Cells(1, lc)).EntireColumn.Hidden = False
    For i = 5 To lc
      Set f = Columns(i).Find(Target.Value, , xlValues, xlPart, , , False)
      If f Is Nothing Then
        Columns(i).EntireColumn.Hidden = True
      End If
    Next
  End If
  Application.ScreenUpdating = True
End Sub

 

[joles]

Put this code in the events of your sheet.
SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Now, select a data in cell D8 or delete the data, the columns will be shown and hidden automatically.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim f As Range
  Dim lc As Long, i As Long
 
  If Target.CountLarge > 1 Then Exit Sub
  Application.ScreenUpdating = False
  If Target.Address(0, 0) = "D8" Then
    lc = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
    Range("E1", Cells(1, lc)).EntireColumn.Hidden = False
    For i = 5 To lc
      Set f = Columns(i).Find(Target.Value, , xlValues, xlPart, , , False)
      If f Is Nothing Then
        Columns(i).EntireColumn.Hidden = True
      End If
    Next
  End If
  Application.ScreenUpdating = True
End Sub

This almost works - the only issue is that I guess it's looking for that value down each full column, rather than only rows 1-7, meaning if in row 8 there's the word "Brand", it's picking up for BRA, if that makes sense?

 

[joles]

Put this code in the events of your sheet.
SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Now, select a data in cell D8 or delete the data, the columns will be shown and hidden automatically.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim f As Range
  Dim lc As Long, i As Long
 
  If Target.CountLarge > 1 Then Exit Sub
  Application.ScreenUpdating = False
  If Target.Address(0, 0) = "D8" Then
    lc = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
    Range("E1", Cells(1, lc)).EntireColumn.Hidden = False
    For i = 5 To lc
      Set f = Columns(i).Find(Target.Value, , xlValues, xlPart, , , False)
      If f Is Nothing Then
        Columns(i).EntireColumn.Hidden = True
      End If
    Next
  End If
  Application.ScreenUpdating = True
End Sub

Made some minor adjustments and got this to work perfectly, thanks!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.