Hide Unapplicable Ranges of Entire Rows

[Guest]

Depending on the integer value displayed in cell L10, I'm trying to hide
unapplicable predefined sets of entire rows, just to make the computations
easier to follow.

For example, if cell L10 displays a value of 3 :myValue), then hide the
entire rows corresponding to myValue of 1, 2, 4, 5, 6, 7, 8, 9
If cell L10 displays a value of 7 :myValue), then hide the entire rows
corresponding to myValue of 1, 2, 3, 4, 5, 6, 8, 9. And so on.

Here is the simple macro that does not work:

Option Base
-------------------------------------------------------------------------------------------
Sub HideOtherScens (mySheet1, mySheet2, myValue)
' mySheet1 & mySheet2 are string arguments
' myValue is integer value in cell L10
'
Dim myName (1 To 9) As String
Dim i As Integer
'
myName (1) = mySheet1.Rows ("12:21") ' corresponds to myValue = 1
myName (2) = mySheet1.Rows ("22:31") ' corresponds to myValue = 2
.............................
myName (9) = mySheet2.Rows ("74:85") ' corresponds to myValue = 9

For i = 1 To 9
If myValue = i Then
myName (i).EntireRow.Hidden = False
Else
myName (i).EntireRow.Hidden = True
End If
Next i
End Su
-------------------------------------------------------------------------------------------

"Compile error: Invalid qualifier", and following the If statement myName is
highlighted.

Where did I go wrong ? Perhaps in the string array declaration ??

Your suggestions would be greatly appreciated.

 

[Dave Peterson]

It looks to me like your using myname(i) as a range variable.

When you use object variables (like ranges, worksheets, workbooks), you have to
use "set".

dim myName(1 to 9) as range
set myName(1) = mySheet1.Rows("12:21")
....

But there may be an easier way. (I'm confused about why your shortened code
used mysheet1 twice and then switched to mysheet2 and why the increment for the
rows changed.)

Option Explicit
Sub testit()
Call HideOtherScens(Sheet1, Sheet2, 9)
End Sub

Sub HideOtherScens(mySheet1 As Worksheet, mySheet2 As Worksheet, myValue)

Dim StartRow As Long
StartRow = (myValue - 1) * 10 + 12

With mySheet1
'all the rows?
'.Rows.Hidden = False
'just 12:85
.Rows("12:85").Hidden = True
.Rows(StartRow).Resize(10).Hidden = False
End With

End Sub

But that grows from 12, 22, 32, ..., 72 and I'm not sure if 74 was a typo or on
purpose.

 

[Guest]

Dave;

Great! Thank you for your help. Your "easier way", however, would not work
because of the different sizes of the entire row ranges.

Following your suggestions, here is the latest version which appears to be
working fine:

Option Base
------------------------------------------------------------------------------------------
Sub GoToMyScen()
Dim mySheet1 As Worksheet
'................................................
myValue = Range ("L10").Value
Set mySheet1 = Worksheets ("Sheet1")
'...............................................
Call HideOtherScens (mySheet1, myValue)
................................................
End Su
------------------------------------------------------------------------------------------
Sub HideOtherScens (mySheet, myScenNum)
'
' to hide all the set ranges on mySheet except the one corresponding to
myScenNum
' mySheet has 9 unequal ranges of entire rows
'
Dim myName (1 To 9) As Range
'
Set myName (1) = mySheet.Rows ("12:20") ' corresponds to myValue = 1
Set myName (2) = mySheet.Rows ("22:32") ' corresponds to myValue = 2
.............................
Set myName (9) = mySheet.Rows ("74:85") ' corresponds to myValue = 9
.............................
For i = 1 To 9
If myScenNum = i Then
myName (i).EntireRow.Hidden = False
Else
myName (i).EntireRow.Hidden = True
End If
Next i
End Su