If ElseIf

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,762
hi all,

Need some help fixing a macro. am trying to hide all rows and only unhide the relevant ones based on what users select from a drop down box.
This is what i have so far but am not on the right track. any one care to help me fix it up? In case you are wondering why my syntax looks really odd, i tried adapting a select case macro to work for the list.

Sub Hide_other_operators(ByVal Target As Range)
Dim Sht As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("f172")) Is Nothing Then Exit Sub
Set Sht = Sheets("Parameters and Assumptions")
With Sht
.Rows("187:2786").RowHeight = 0
ElseIf Target = "QRail" Then
.Rows("187:339").AutoFit
ElseIf Target = "Bribie" Then
.Rows("340:492").AutoFit
ElseIf Target = "BrisbaneTransport" Then
.Rows("493:645").AutoFit
ElseIf Target = "BCCFerries" Then
.Rows("646:798").AutoFit
ElseIf Target = "Buslink" Then
.Rows("799:951").AutoFit
ElseIf Target = "Caboolture" Then
.Rows("952:1104").AutoFit
ElseIf Target = "Clarks" Then
.Rows("1105:1257").AutoFit
ElseIf Target = "Hornibrook" Then
.Rows("1258:1410").AutoFit
ElseIf Target = "Kangaroo" Then
.Rows("1411:1563").AutoFit
ElseIf Target = "MtGravatt" Then
.Rows("1564:1716").AutoFit
ElseIf Target = "National" Then
.Rows("1717:1869").AutoFit
ElseIf Target = "ParkRidge" Then
.Rows("1870:2022").AutoFit
ElseIf Target = "Sunbus" Then
.Rows("2023:2175").AutoFit
ElseIf Target = "Thompson" Then
.Rows("2176:2328").AutoFit
ElseIf Target = "Westside" Then
.Rows("2329:2481").AutoFit
ElseIf Target = "SouthernCross" Then
.Rows("2482:2634").AutoFit
ElseIf Target = "Surfside" Then
.Rows("2635:2787").AutoFit
ElseIf Target = "AllOPerators" Then
.Rows("187:2940").AutoFit
End If
End Sub
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
Is loolks line Worksheet_Change event code, so
change
Code:
Sub Hide_other_operators(ByVal Target As Range)
To
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
And paste the code onto sheet module.
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,762
can't get that to work either. anything else come to mind?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
ajm

Did you paste the new code onto sheet module?
If so, can you post your entire sheet module code(s)?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

ajm

Stick to your original thread!!
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,762
jindon. cleaned up some fluff that I had ben mucking around with and it now works fine. many thanks for your help.
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,762
jindon, i have one lingering problem. this is the entire macro:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Sht As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("f172")) Is Nothing Then Exit Sub
Set Sht = Sheets("Parameters and Assumptions")
With Sht
.Rows("187:2786").RowHeight = 0
If Target = "QRail" Then
.Rows("187:337").AutoFit
ElseIf Target = "Bribie" Then
.Rows("340:492").AutoFit
ElseIf Target = "BrisbaneTransport" Then
.Rows("493:645").AutoFit
ElseIf Target = "BCCFerries" Then
.Rows("646:798").AutoFit
ElseIf Target = "Buslink" Then
.Rows("799:951").AutoFit
ElseIf Target = "Caboolture" Then
.Rows("952:1104").AutoFit
ElseIf Target = "Clarks" Then
.Rows("1105:1257").AutoFit
ElseIf Target = "Hornibrook" Then
.Rows("1258:1410").AutoFit
ElseIf Target = "Kangaroo" Then
.Rows("1411:1563").AutoFit
ElseIf Target = "MtGravatt" Then
.Rows("1564:1716").AutoFit
ElseIf Target = "National" Then
.Rows("1717:1869").AutoFit
ElseIf Target = "ParkRidge" Then
.Rows("1870:2022").AutoFit
ElseIf Target = "Sunbus" Then
.Rows("2023:2175").AutoFit
ElseIf Target = "Thompson" Then
.Rows("2176:2328").AutoFit
ElseIf Target = "Westside" Then
.Rows("2329:2481").AutoFit
ElseIf Target = "SouthernCross" Then
.Rows("2482:2634").AutoFit
ElseIf Target = "Surfside" Then
.Rows("2635:2787").AutoFit
ElseIf Target = "AllOPerators" Then
.Rows("187:2940").AutoFit
End If
End With
End Sub

The problem occurs when the user selects the first option in the drop down box: QRail. In the macro, the only way i could get it to run was to put this in:

If Target = "QRail" Then
.Rows("187:337").AutoFit

as the macro required an initial "If". where else can I put this IF so that if the user selects QRAIL, only the QRail section is shown and the rest hidden?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,051
Messages
5,545,724
Members
410,702
Latest member
clizama18
Top