If ElseIf

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,854
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

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,854
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,854
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,854
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?
 

Forum statistics

Threads
1,141,912
Messages
5,709,287
Members
421,625
Latest member
Natalie1107

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top