VBA Code - Refer to Range and Rows

[tlc53]

Hi there,

Can I refer to a range and row reference in one line of code?

Eg.

Case 0      Range("208:604").Rows("619, 629").EntireRow.Hidden = True

So I would like to hide range "208:604" and single rows, 619 and 629. The above doesn't work.

Thank you!

 

[Rick Rothstein]

Try it this way...

Range("208:604,619:619,629:629").EntireRow.Hidden = True

 

[tlc53]

Hi. Thanks.

The problem is I have to refer to 80 singular rows along with multiple ranges, so if I mark my single rows as ranges, it gets too long and won't accept the code.

 

[Rick Rothstein]

Hi. Thanks.

The problem is I have to refer to 80 singular rows along with multiple ranges, so if I mark my single rows as ranges, it gets too long and won't accept the code.

Hmm, I don't remember you telling us that in your original message.

Okay, in that case, you will need to run a loop. Put your row ranges into a space delimited text string as shown.

Dim V As Variant

For Each V In Split("208:604 619 629")
  Rows(V).Hidden = True
Next

 

[tlc53]

Whoops! Sorry
Thanks for that! Works perfectly!

 

[tlc53]

Actually, it worked fine on the first Case but once I started copying the code for each Case, I started to run into problems.

I have decided to attack it from another angle.

How do I list multiple singular rows in this line of code? Is it possible?

    Case "Please Select"
        Rows("619,629,630,640").EntireRow.Hidden = True

The above doesn't work.

 

[Rick Rothstein]

Actually, it worked fine on the first Case but once I started copying the code for each Case, I started to run into problems.

I have decided to attack it from another angle.

How do I list multiple singular rows in this line of code? Is it possible?

    Case "Please Select"
        Rows("619,629,630[B][COLOR="#FF0000"]:630[/COLOR][/B],640[B][COLOR="#FF0000"]:640[/COLOR][/B]").EntireRow.Hidden = True

The above doesn't work.

You have to supply a "complete" range (row colon row) even for a single range (just put a colon and repeat the single row's number)... just add what I show in red above.

 

[tlc53]

Oh, okay. Thanks for clarifying