How to refer to multiple variables

[amauer]

I am trying to select a range of rows, then hide or unhide them. here is some sample code:

Dim Var1 as range
Dim Var2 as range

Set Var1 = Sheets("Sample").Range("22:25")
Set Var2 = Sheets("Sample").Range("28:33")

Range("Var1,Var2").EntireRow.Hidden = True (this is the error line)

I get an error, but it will work if I do this:

Var1.EntireRow.Hidden = True

The issue is that my list of varibles is 28 and must be interchangeable.

Thanks for any suggestions!

 

[amauer]

My test sheet was only using 2 variables, but my production sheet is using several. When I add a third, I get: Compile error: Wrong number of arguments or invalid property assignment.

Range(Var1,Var2,Var3).EntireRow.Hidden = false

is there a way to add a third, fourth... some could have up to 8.

 

[Joe4]

This works:

Dim Var1 As Range
Dim Var2 As Range
Dim Var3 As Range
Dim rng As Range

Set Var1 = Sheets("Sample").Range("22:25")
Set Var2 = Sheets("Sample").Range("28:33")
Set Var3 = Sheets("Sample").Range("38:41")
Set rng = Union(Var1, Var2, Var3)

rng.EntireRow.Hidden = True

 

[Rick Rothstein]

My test sheet was only using 2 variables, but my production sheet is using several. When I add a third, I get: Compile error: Wrong number of arguments or invalid property assignment.

Range(Var1,Var2,Var3).EntireRow.Hidden = false

is there a way to add a third, fourth... some could have up to 8.

When supply Range objects as arguments to a Range object, you can only have two of them... and those two define a contiguous overall range, not two separate individual ranges. To get several individual ranges, the addresses must be passed as a comma delimited text string.

[table="width: 500"]
[tr]
	[td]Range(Var1.Address & "," & Var2.Address & "," & Var3.Address).EntireRow.Hidden = False[/td]
[/tr]
[/table]

But note that the concatenated text string argument is limited to a maximum of 255 characters total. To aid in keeping the length of the argument as small as possible, you should specify 0,0 as an argument to the Address function to suppress the $ signs it supplies when no argument is provided...

[table="width: 500"]
[tr]
	[td]Range(Var1.Address(0, 0) & "," & Var2.Address(0, 0) & "," & Var3.Address(0, 0)).EntireRow.Hidden = False[/td]
[/tr]
[/table]

Alternately, you can use the Union method as Joe outlined in his response.

 

[amauer]

Thanks for all the help! Range working in different ways was my issue. I was able to set Var1 with many ranges (8+), but could not use more than 2 variables as ranges.