If then else if problem for DA

[DA]

I know this macro doesn't work (bad syntax) and I also know there must
be more elegant ways to do this. Kindly help.

Sub HideUnusedHurdles()

If Range("NumberOfHurdles").Value = 4 Then GoTo 100

Else If Range("NumberOfHurdles").Value = 3 Then
Range("HideMonthlyRowsIfOnlyThreeHurdles").Select
Selection.EntireRow.Hidden = True
Range("HideQtrlyRowsIfOnlyThreeHurdles").Select
Selection.EntireRow.Hidden = True
Range("HideAnnualRowsIfOnlyThreeHurdles").Select
Selection.EntireRow.Hidden = True
GoTo 100



Else If Range("NumberOfHurdles").Value = 2 Then
Range("HideMonthlyRowsIfOnlyTwoHurdles").Select
Selection.EntireRow.Hidden = True
Range("HideQtrlyRowsIfOnlyTwoHurdles").Select
Selection.EntireRow.Hidden = True
Range("HideAnnualRowsIfOnlyTwoHurdles").Select
Selection.EntireRow.Hidden = True
GoTo 100

Else If Range("NumberOfHurdles").Value = 1 Then
Range("HideMonthlyRowsIfOnlyOneHurdle").Select
Selection.EntireRow.Hidden = True
Range("HideQtrlyRowsIfOnlyOneHurdle").Select
Selection.EntireRow.Hidden = True
Range("HideAnnualRowsIfOnlyOneHurdle").Select
Selection.EntireRow.Hidden = True
100
End If

End Sub

 

[Marcel]

I'm not quite sure what you want to achieve, how are these different
ranges you wish to hide/unhide related to one another, are they just
below one another for example?

Here's something that MIGHT help you along a little bit. In one of my
sheets, I have approx. 14 ranges of 13 rows (1 title and totals and 12
months). To hide/unhide the monthly rows, depending on the hidden/
unhidden property of the title row:

Dim IntMndZb(1 to 12) as Integer, Smallrng as Range

For i = 1 To 12

If IntMndZb(i) = 1 Then 'IntMndZb is the variable I use to keep
track of whether Month X should be visible at all
For Each Smallrng In
Range("B4,B18,B32,B46,B60,B74,B88,B102,B116,B130,B144,B158,B172,B186,B200,B214")
If Smallrng.Rows.Hidden Then
Smallrng.Offset(i, 0).EntireRow.Hidden = True
Else
Smallrng.Offset(i, 0).EntireRow.Hidden = False
End If
Next Smallrng
Else
For Each Smallrng In
Range("B4,B18,B32,B46,B60,B74,B88,B102,B116,B130,B144,B158,B172,B186,B200,B214")
If Smallrng.Rows.Hidden Then
Smallrng.Offset(i, 0).EntireRow.Hidden = True
Else
Smallrng.Offset(i, 0).EntireRow.Hidden = True
End If
Next Smallrng
End If
Next i


If your ranges are easily identified, e.g.
Range("HideMonthlyRowsIfOnlyTwoHurdles") is always one row below the
'IfOnlyThree' and two rows below 'IfFourHurdles", a loop like this
might help.

Good luck, Marcel

 

[Ken]

DA

I am not sure exactly what you are trying to do, but, I recommend you
look into the Select Case statement, and get rid of your range
selections. The following doesn't do everything you want, but, it
should give your some ideas to get you started.

Sub test()

Dim H_1 As Range
Dim H_2 As Range
Dim H_3 As Range

Dim H As Integer

Set H_1 = ActiveSheet.Range("Hideif2Hurdles")
Set H_2 = ActiveSheet.Range("Hideif3Hurdles")
Set H_3 = ActiveSheet.Range("Hideif3Hurdles")

H = ActiveSheet.Range("NumberofHurdles").Value

Select Case H

Case 1
H_1.EntireRow.Hidden = False
H_2.EntireRow.Hidden = True
H_3.EntireRow.Hidden = True

Case 2

H_1.EntireRow.Hidden = True
H_2.EntireRow.Hidden = False
H_3.EntireRow.Hidden = True

Case 3


H_1.EntireRow.Hidden = True
H_2.EntireRow.Hidden = True
H_3.EntireRow.Hidden = False

Case Else

End Select

End Sub

Good luck.

Ken
Norfolk, Va

 

[DA]

Yes, this is much easier. Thank you both very much.