jjmnb
09-29-2017, 08:14 AM
Hi,
I'm pretty much a novice at VBA, but have been trying to work on a fillable sheet where there is a drop down function in cell C2 that contains a list of countries. When a country is selected, I want certain non-contiguous rows in the same sheet to be hidden. When another country is selected, the same concept applies, but potentially some of the previous hidden rows are unhidden, and some of the previously shown rows are hidden.
I have this aspect working, but when any entry is made in the sheet the macro stops working and all rows are unhidden. Hopefully someone can give me some guidance as to how to have this work, even if that includes some form of button so the macro only works when pressed? My attempt at the code is below:
Thanks in advance!
======================================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Select Country" Then
Rows("4:75").Hidden = True
Else
Rows("4:75").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Australia" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Austria" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Belgium" Then
Rows("7:68").Hidden = True
Range("7:9,11:14,18:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Canada" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Chile" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Czech Republic" Then
Rows("7:68").Hidden = True
Range("8:10,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Denmark" Then
Rows("7:68").Hidden = True
Range("8:10,11:14,19:22,24:27,29:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Finland" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "France" Then
Rows("7:68").Hidden = True
Range("7:9,11:14,19:23,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Germany" Then
Rows("7:68").Hidden = True
Range("8:9,10:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Greece" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Hungary" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,68:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Ireland" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Italy" Then
Rows("7:68").Hidden = True
Range("7:9,10:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Japan" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:23,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Luxembourg" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Netherlands" Then
Rows("7:68").Hidden = True
Range("7:9,11:14,19:22,24:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Norway" Then
Rows("7:68").Hidden = True
Range("8:9,10:17,19:22,24:27,29:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Poland" Then
Rows("7:68").Hidden = True
Range("8:9,11:17,19:22,52:67,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Portugal" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Slovak Republic" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "South Korea" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Spain" Then
Rows("7:68").Hidden = True
Range("7:9,10:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Sweden" Then
Rows("7:68").Hidden = True
Range("8:9,11:17,19:22,24:27,29:29,32:34,37:39,42:44,47:49,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Switzerland" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "United Kingdom" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
I'm pretty much a novice at VBA, but have been trying to work on a fillable sheet where there is a drop down function in cell C2 that contains a list of countries. When a country is selected, I want certain non-contiguous rows in the same sheet to be hidden. When another country is selected, the same concept applies, but potentially some of the previous hidden rows are unhidden, and some of the previously shown rows are hidden.
I have this aspect working, but when any entry is made in the sheet the macro stops working and all rows are unhidden. Hopefully someone can give me some guidance as to how to have this work, even if that includes some form of button so the macro only works when pressed? My attempt at the code is below:
Thanks in advance!
======================================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Select Country" Then
Rows("4:75").Hidden = True
Else
Rows("4:75").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Australia" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Austria" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Belgium" Then
Rows("7:68").Hidden = True
Range("7:9,11:14,18:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Canada" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Chile" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Czech Republic" Then
Rows("7:68").Hidden = True
Range("8:10,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Denmark" Then
Rows("7:68").Hidden = True
Range("8:10,11:14,19:22,24:27,29:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Finland" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "France" Then
Rows("7:68").Hidden = True
Range("7:9,11:14,19:23,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Germany" Then
Rows("7:68").Hidden = True
Range("8:9,10:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Greece" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Hungary" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,68:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Ireland" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Italy" Then
Rows("7:68").Hidden = True
Range("7:9,10:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Japan" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:23,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Luxembourg" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Netherlands" Then
Rows("7:68").Hidden = True
Range("7:9,11:14,19:22,24:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Norway" Then
Rows("7:68").Hidden = True
Range("8:9,10:17,19:22,24:27,29:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Poland" Then
Rows("7:68").Hidden = True
Range("8:9,11:17,19:22,52:67,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Portugal" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Slovak Republic" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "South Korea" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Spain" Then
Rows("7:68").Hidden = True
Range("7:9,10:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Sweden" Then
Rows("7:68").Hidden = True
Range("8:9,11:17,19:22,24:27,29:29,32:34,37:39,42:44,47:49,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "Switzerland" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
If Target.Column = 3 And Target.Row = 2 And Target.Value = "United Kingdom" Then
Rows("7:68").Hidden = True
Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
Else
Rows("7:68").Hidden = False
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub