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!

 

[sheetspread]

Try it without the quotes

That will hide the rows in between also though. Otherwise you can have one array variable and save the ranges there, then loop through.

 

[amauer]

I removed the quotes, Range(Var1,Var2).En...., but I still receive the same error. I also get the error when I only have one variable.

Looping is an issue, my variables have descriptive names.

 

[Joe4]

This worked just fine for me:

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

What is the exact error message you get when trying this?

 

[amauer]

Run-time error '1004':
Method 'Range' of Object'_Global' failed

 

[Joe4]

Do you have a sheet named "Sample"?
Do you have any merged cells on the "Sample" sheet?
Are there any protected ranges on the sheet?

 

[amauer]

Yes, sheet name is correct.
I have merged cells, but the merges do not overlap the ranges.
Nothing is protected.

I am able to use Var1.EntireRow.Hidden = True, but would like to simply by not adding a list of these. I would like to incorporate the line with all variables I need.

 

[amauer]

I will look it over for errors, thanks.

 

[Joe4]

Check to see if this works:

[COLOR=#333333]Var2.EntireRow.Hidden = True[/COLOR]

If that doesn't, then you know where to look for issues.

 

[Rick Rothstein]

This worked just fine for me:

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

It worked fine for me as well. Not sure where the error will turn out to be, but I wanted to point out to the OP that he did not need to use two separate variables to do his hiding... this also works.

Dim Var As Range

Set Var = Sheets("Sample").Range("22:25,28:33")

Var.EntireRow.Hidden = True