Need help with hiding non-contiguous rows

Question

I am relatively new to VBA and I searched and found the following code to hide contiguous rows:

Worksheets("Sheet1").Rows("7:12").Hidden = True

However, I would like to hide non-contiguous rows using a single statement (like the one above).  Unfortunately, the line above appears to accepts only a single range.

Can someone please tell me how I can modify the line above to accept multiple ranges?  (I already tried using "7:12,15:20", but I get a Type Mismatch error message).

Thanks in advance for any help.

Bob

Answer 1

...I noticed that you enclosed the range name "MyRowsToHide" in square brackets in the VBA code.  Is that because it is a range name?  Is that the general rule when referencing range names?...

When referencing a range directly, it must be enclosed in square brackets.

This: [MyRange].Rows

is equivalent to: Range("MyRange").Rows

---

Ron Coderre

Microsoft MVP (2006 - 2010) - Excel

P.S. If any post answers your question, please mark it as the Answer (so it won't keep showing as an open item.)

Answer 2

Use Range rather than Rows:

Sub hideUm()

Range("A1,A3,A7").EntireRow.Hidden = True

End Sub

---

gsnu201004

Answer 3

1. There's practiaclly no VBA code at all in the suggested approach  posted. I assigned a Named Range to non-contiguous ranges of cells and referenced that named range in the macro.

Example:

To assign a Named Range to rows 1:4, 7:10, 15:16 and 22:24

• Select Rows 1:4

• Hold down the CTRL key while you then select:

...Rows 7:10

...Rows 15:16

...Rows 22:24

• Releast the CTRL key  (rows 1:4, 7:10, 15:16 and 22:24 will all be selected)

• In the Name Box, in the upper left of the sheet, just above the Col_A header

...Type: MyRowsToHide

Now, this VBA command will hide those rows:

[MyRowsToHide].entirerow.hidden = TRUE

and this one will UNhide them:

[MyRowsToHide].entirerow.hidden = FALSE

The upside of that method is that you don't need to touch the VBA code when you need to change the rows to be hidden/unhidden.  Just change the rows referenced in the Named Range.

2. However, if you want all of the reference activity to happen within the code.

Try something like this to hide rows 1:4, 7:10, 15:16 and 22:24

Sub HideMyRows()

Dim MyRowsToHide

With ActiveSheet

    Set MyRowsToHide = Application.Union(.Range("1:4"), .Range("7:10"), .Range("15:16"), .Range("22:24"))

End With

MyRowsToHide.EntireRow.Hidden = True

End Sub

Does that help?

---

Ron Coderre

Microsoft MVP (2006 - 2010) - Excel

P.S. If any post answers your question, please mark it as the Answer (so it won't keep showing as an open item.)

Answer 4

Ron,

Thanks for your help.  I really appreciate it.  Unfortunately, you provided a level of code sophistication that is way beyond my current level of VBA expertise.

Instead, I would prefer to simply learn how to incorporate a non-contiguous range such as "7:12,15:20" using a single statement like the one I mentioned in my original post.

Can you help?

Regards,

Bob

Answer 5

If the same rows are hidden regularly, perhaps you could assign a range name to them and hide the rows of that named range.

Example:

• I assigned a named range, as follows, to hide rows 1:4, 7:10, 15:16 and 22:24

Formula.Named_Manager.New

...Name: MyRowsToHide

...Scope: Workbook

...Refers to: =Sheet1!$A$1:$A$4,Sheet1!$A$7:$A$10,Sheet1!$A$15:$A$16,Sheet1!$A$22:$A$24

Click: OK

Now, this VBA command will hide those rows:

[MyRowsToHide].entirerow.hidden = TRUE

and this one will UNhide them:

[MyRowsToHide].entirerow.hidden = FALSE

Is that something you can work with?

---

Ron Coderre

Microsoft MVP (2006 - 2010) - Excel

P.S. If any post answers your question, please mark it as the Answer (so it won't keep showing as an open item.)