VBA If Target.column as drop down list

[MSpears93]

Hi,
I'm using VBA as a drop down so that when I click on the selection it hides all columns except for the ones I need to see for that criteria.

My problem is that once I have selected one of the options e.g Newcastle, to see the next option e.g. Carlisle, I have to go back to Select College and then select Carlisle, otherwise it shows nothing.

I want to be able to select any college without having to go back to Select College. I hope this makes sense.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Row = 1 Then
        If Target.Value = "Carlisle" Then
            Range("B:R,AJ:DP").EntireColumn.Hidden = True
        ElseIf Target.Value = "Kidderminster" Then
            Range("B:AI,BA:DP").EntireColumn.Hidden = True
        ElseIf Target.Value = "Lewisham" Then
            Range("B:AZ,BR:DP").EntireColumn.Hidden = True
        ElseIf Target.Value = "West Lancashire" Then
            Range("B:CY").EntireColumn.Hidden = True
         ElseIf Target.Value = "Southwark" Then
            Range("B:CH,CZ:DP").EntireColumn.Hidden = True
        ElseIf Target.Value = "Newcastle" Then
            Range("B:BQ,CI:DP").EntireColumn.Hidden = True
        ElseIf Target.Value = "Select College" Then
            Range("B:DP").EntireColumn.Hidden = False
                        

End If
End If
   

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about adding this line as shown

    If Target.Column = 1 And Target.Row = 1 Then
        Range("B:DP").EntireColumn.Hidden = False
        If Target.Value = "Carlisle" Then

 

[MSpears93]

Hi & welcome to MrExcel.
How about adding this line as shown

    If Target.Column = 1 And Target.Row = 1 Then
        Range("B:DP").EntireColumn.Hidden = False
        If Target.Value = "Carlisle" Then

That's worked perfectly!! Thank you

 

[Fluff]

You're welcome & thanks for the feedback.