Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If then else if problem for 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If then else if problem for DA
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,B 144,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,B 144,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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If then else if problem for DA
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 On Oct 6, 6:24*pm, DA wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If then else if problem for DA
Yes, this is much easier. Thank you both very much.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |