Improved VBA Code to hide/unhide columns in a range

[Addisonian]

My VBA knowledge is very fresh and I imagine that shows in the code below. I have used the below code before to hide and unhide columns based on a small dropdown list. The list I am working off of here is a dropdown of a dropdown which means the total amount of columns I am looking at is 80+ and will continue to grow.

What I am trying to do is more or less is:

Hide columns (L:CU)=True [with the easy ability to move CU down the line] unless "row 7" of a column equals Target.Address (cell "I3")

Ideally done off a change event.

If Target.Address = ("$I$3") Then
        If Target.Text = "Countryside" Then
            Columns("M:CT").EntireColumn.Hidden = True
            Columns("M").EntireColumn.Hidden = False
            
        ElseIf Target.Text = "Yorktown" Then
            Columns("M:CT").EntireColumn.Hidden = True
            Columns("N").EntireColumn.Hidden = False
            
        ElseIf Target.Text = "McLean" Then
            Columns("M:CT").EntireColumn.Hidden = True
            Columns("O").EntireColumn.Hidden = False
    
        ElseIf Target.Text = "Bella Vista Traditional" Then
            Columns("M:CT").EntireColumn.Hidden = True
            Columns("P").EntireColumn.Hidden = False
            
        ElseIf Target.Text = "New Haven" Then
            Columns("M:CT").EntireColumn.Hidden = True
            Columns("Q").EntireColumn.Hidden = False

Thanks for any help.

 

[Fluff]

Do you mean hide all columns from L to CU except those that have the same value as I3 in row 7 of that column?

 

[Addisonian]

Do you mean hide all columns from L to CU except those that have the same value as I3 in row 7 of that column?

Exactly.

Thanks for the reply - Sorry for the misleading title it was a typo, should say "Help Improve..."

 

[Fluff]

Ok, is row 7 hard values, or formulae?

 

[Addisonian]

Ok, is row 7 hard values, or formulae?

Hard values - Text to be specific.

With I3 being a dropdown.

 

[My Aswer Is This]

I would use this script using Case instead of If.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/8/2021  2:06:11 PM  EDT

If Target.Address = ("$I$3") Then
        
        Select Case Target.Value
        Case "Countryside"
            Columns("M:CT").EntireColumn.Hidden = True
            Columns("M").EntireColumn.Hidden = False
        Case "Countryside"
            Columns("M:CT").EntireColumn.Hidden = True
            Columns("M").EntireColumn.Hidden = False
        Case "Yorktown"
            Columns("M:CT").EntireColumn.Hidden = True
            Columns("N").EntireColumn.Hidden = False
        Case "McLean"
            Columns("M:CT").EntireColumn.Hidden = True
            Columns("O").EntireColumn.Hidden = False
        Case "Bella Vista Traditional"
            Columns("M:CT").EntireColumn.Hidden = True
            Columns("P").EntireColumn.Hidden = False
        Case "New Have"
            Columns("M:CT").EntireColumn.Hidden = True
            Columns("Q").EntireColumn.Hidden = False
            
End Select
End If
End Sub

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cl As Range
   
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "I3" Then
      Columns("L:CU").Hidden = True
         If Cl.Value = Target.Value Then Cl.EntireColumn.Hidden = False
      Next Cl
   End If
End Sub

 

[Addisonian]

That doesn't seem to work - and unfortunately since I am pretty new to all this I am not sure why.

The debug pops a "Compile error: Next without For" if that helps?

 

[My Aswer Is This]

That doesn't seem to work - and unfortunately since I am pretty new to all this I am not sure why.

The debug pops a "Compile error: Next without For" if that helps?

I do not believe the next should be there. Just remove it.

 

[Fluff]

Oops, I missed a line

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cl As Range
   
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "I3" Then
      Columns("L:CU").Hidden = True
      For Each Cl In Range("L7:CU7")
         If Cl.Value = Target.Value Then Cl.EntireColumn.Hidden = False
      Next Cl
   End If
End Sub