ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Macro help (https://www.excelbanter.com/excel-programming/426729-macro-help.html)

Leiprecht
Macro help
 
Here is my macro. The issue that I am having is it does not work backward.
Meaning. If cell "L62" has the value of 14, and you change it to 10 it does
not go back to the values according to value 10. It stays at the values of
14. However, it does work if you go back to value 0 then select 10. But I
want to avoid that step is possible.

If Range("L62").Value = 0 Then
Range("63:147").EntireRow.Hidden = True
ElseIf Range("L62").Value = 1 Then
Range("63:69").EntireRow.Hidden = False
ElseIf Range("L62").Value = 2 Then
Range("63:75").EntireRow.Hidden = False
ElseIf Range("L62").Value = 3 Then
Range("63:81").EntireRow.Hidden = False
ElseIf Range("L62").Value = 4 Then
Range("63:87").EntireRow.Hidden = False
ElseIf Range("L62").Value = 5 Then
Range("63:93").EntireRow.Hidden = False
ElseIf Range("L62").Value = 6 Then
Range("63:99").EntireRow.Hidden = False
ElseIf Range("L62").Value = 7 Then
Range("63:105").EntireRow.Hidden = False
ElseIf Range("L62").Value = 8 Then
Range("63:111").EntireRow.Hidden = False
ElseIf Range("L62").Value = 9 Then
Range("63:117").EntireRow.Hidden = False
ElseIf Range("L62").Value = 10 Then
Range("63:123").EntireRow.Hidden = False
ElseIf Range("L62").Value = 11 Then
Range("63:129").EntireRow.Hidden = False
ElseIf Range("L62").Value = 12 Then
Range("63:135").EntireRow.Hidden = False
ElseIf Range("L62").Value = 13 Then
Range("63:141").EntireRow.Hidden = False
ElseIf Range("L62").Value = 14 Then
Range("63:147").EntireRow.Hidden = False
End If

Per Jessen
Macro help
 
Hide all rows by default, then test which rows to unhide:

Range("63:147").EntireRow.Hidden = True
If Range("L62").Value = 1 Then
Range("63:69").EntireRow.Hidden = False
ElseIf Range("L62").Value = 2 Then
Range("63:75").EntireRow.Hidden = False
ElseIf Range("L62").Value = 3 Then
Range("63:81").EntireRow.Hidden = False
ElseIf Range("L62").Value = 4 Then
......

Regards,
Per

"Leiprecht" skrev i meddelelsen
...
Here is my macro. The issue that I am having is it does not work
backward.
Meaning. If cell "L62" has the value of 14, and you change it to 10 it
does
not go back to the values according to value 10. It stays at the values
of
14. However, it does work if you go back to value 0 then select 10. But I
want to avoid that step is possible.

If Range("L62").Value = 0 Then
Range("63:147").EntireRow.Hidden = True
ElseIf Range("L62").Value = 1 Then
Range("63:69").EntireRow.Hidden = False
ElseIf Range("L62").Value = 2 Then
Range("63:75").EntireRow.Hidden = False
ElseIf Range("L62").Value = 3 Then
Range("63:81").EntireRow.Hidden = False
ElseIf Range("L62").Value = 4 Then
Range("63:87").EntireRow.Hidden = False
ElseIf Range("L62").Value = 5 Then
Range("63:93").EntireRow.Hidden = False
ElseIf Range("L62").Value = 6 Then
Range("63:99").EntireRow.Hidden = False
ElseIf Range("L62").Value = 7 Then
Range("63:105").EntireRow.Hidden = False
ElseIf Range("L62").Value = 8 Then
Range("63:111").EntireRow.Hidden = False
ElseIf Range("L62").Value = 9 Then
Range("63:117").EntireRow.Hidden = False
ElseIf Range("L62").Value = 10 Then
Range("63:123").EntireRow.Hidden = False
ElseIf Range("L62").Value = 11 Then
Range("63:129").EntireRow.Hidden = False
ElseIf Range("L62").Value = 12 Then
Range("63:135").EntireRow.Hidden = False
ElseIf Range("L62").Value = 13 Then
Range("63:141").EntireRow.Hidden = False
ElseIf Range("L62").Value = 14 Then
Range("63:147").EntireRow.Hidden = False
End If


Leiprecht
Macro help
 
Thanks! That worked!

"Per Jessen" wrote:

Hide all rows by default, then test which rows to unhide:

Range("63:147").EntireRow.Hidden = True
If Range("L62").Value = 1 Then
Range("63:69").EntireRow.Hidden = False
ElseIf Range("L62").Value = 2 Then
Range("63:75").EntireRow.Hidden = False
ElseIf Range("L62").Value = 3 Then
Range("63:81").EntireRow.Hidden = False
ElseIf Range("L62").Value = 4 Then
......

Regards,
Per

"Leiprecht" skrev i meddelelsen
...
Here is my macro. The issue that I am having is it does not work
backward.
Meaning. If cell "L62" has the value of 14, and you change it to 10 it
does
not go back to the values according to value 10. It stays at the values
of
14. However, it does work if you go back to value 0 then select 10. But I
want to avoid that step is possible.

If Range("L62").Value = 0 Then
Range("63:147").EntireRow.Hidden = True
ElseIf Range("L62").Value = 1 Then
Range("63:69").EntireRow.Hidden = False
ElseIf Range("L62").Value = 2 Then
Range("63:75").EntireRow.Hidden = False
ElseIf Range("L62").Value = 3 Then
Range("63:81").EntireRow.Hidden = False
ElseIf Range("L62").Value = 4 Then
Range("63:87").EntireRow.Hidden = False
ElseIf Range("L62").Value = 5 Then
Range("63:93").EntireRow.Hidden = False
ElseIf Range("L62").Value = 6 Then
Range("63:99").EntireRow.Hidden = False
ElseIf Range("L62").Value = 7 Then
Range("63:105").EntireRow.Hidden = False
ElseIf Range("L62").Value = 8 Then
Range("63:111").EntireRow.Hidden = False
ElseIf Range("L62").Value = 9 Then
Range("63:117").EntireRow.Hidden = False
ElseIf Range("L62").Value = 10 Then
Range("63:123").EntireRow.Hidden = False
ElseIf Range("L62").Value = 11 Then
Range("63:129").EntireRow.Hidden = False
ElseIf Range("L62").Value = 12 Then
Range("63:135").EntireRow.Hidden = False
ElseIf Range("L62").Value = 13 Then
Range("63:141").EntireRow.Hidden = False
ElseIf Range("L62").Value = 14 Then
Range("63:147").EntireRow.Hidden = False
End If



Dana DeLouis[_3_]
Macro help
 
ElseIf Range("L62").Value = 1 Then
Range("63:69").EntireRow.Hidden = False
ElseIf Range("L62").Value = 2 Then
Range("63:75").EntireRow.Hidden = False


Hi. I can't test this now, but here's an idea...

Range("63:147").EntireRow.Hidden = True
k = CLng(Range("L62"))
Select Case k
Case 1 to 14
Row(63:63).Resize(2*k).Hidden = False
End Select

Dana DeLouis


Leiprecht wrote:
Thanks! That worked!

"Per Jessen" wrote:

Hide all rows by default, then test which rows to unhide:

Range("63:147").EntireRow.Hidden = True
If Range("L62").Value = 1 Then
Range("63:69").EntireRow.Hidden = False
ElseIf Range("L62").Value = 2 Then
Range("63:75").EntireRow.Hidden = False
ElseIf Range("L62").Value = 3 Then
Range("63:81").EntireRow.Hidden = False
ElseIf Range("L62").Value = 4 Then
......

Regards,
Per

"Leiprecht" skrev i meddelelsen
...
Here is my macro. The issue that I am having is it does not work
backward.
Meaning. If cell "L62" has the value of 14, and you change it to 10 it
does
not go back to the values according to value 10. It stays at the values
of
14. However, it does work if you go back to value 0 then select 10. But I
want to avoid that step is possible.

If Range("L62").Value = 0 Then
Range("63:147").EntireRow.Hidden = True
ElseIf Range("L62").Value = 1 Then
Range("63:69").EntireRow.Hidden = False
ElseIf Range("L62").Value = 2 Then
Range("63:75").EntireRow.Hidden = False
ElseIf Range("L62").Value = 3 Then
Range("63:81").EntireRow.Hidden = False
ElseIf Range("L62").Value = 4 Then
Range("63:87").EntireRow.Hidden = False
ElseIf Range("L62").Value = 5 Then
Range("63:93").EntireRow.Hidden = False
ElseIf Range("L62").Value = 6 Then
Range("63:99").EntireRow.Hidden = False
ElseIf Range("L62").Value = 7 Then
Range("63:105").EntireRow.Hidden = False
ElseIf Range("L62").Value = 8 Then
Range("63:111").EntireRow.Hidden = False
ElseIf Range("L62").Value = 9 Then
Range("63:117").EntireRow.Hidden = False
ElseIf Range("L62").Value = 10 Then
Range("63:123").EntireRow.Hidden = False
ElseIf Range("L62").Value = 11 Then
Range("63:129").EntireRow.Hidden = False
ElseIf Range("L62").Value = 12 Then
Range("63:135").EntireRow.Hidden = False
ElseIf Range("L62").Value = 13 Then
Range("63:141").EntireRow.Hidden = False
ElseIf Range("L62").Value = 14 Then
Range("63:147").EntireRow.Hidden = False
End If


Dana DeLouis[_3_]
Macro help
 
Oops! Just noticed the typo. The differences are 6, not 2.

Range("63:147").EntireRow.Hidden = True
k = CLng(Range("L62"))
Select Case k
Case 1 to 14
Row(63:63).Resize(6*k).Hidden = False
End Select

Dana DeLouis


<snip


All times are GMT +1. The time now is 05:40 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com