Multiple Ranges in Hide Row Code

[Anders]

Hi All,
I have code that works to hide rows with < 0 value for a specific range. I
have 20+ ranges I need to specify (obviosly none are sequential or it would
be a much larger range). It works with 2 ranges specified, but not with any
more. Below is the working and non working code. Is the solution some sort
of Set Range at the beginning of the sub? I tried it but couldn't get it to
work.

''' Working Code
Sub hide_row2()
For Each cell In Range("B21:B26", "B30:B51")
cell.EntireRow.Hidden = cell.Value < 1
Next cell
End Sub

''' Non Working Code
Sub hide_row()
For Each cell In Range("B21:26", "B30:B51", "B57:B90", "B95:B112")
cell.EntireRow.Hidden = cell.Value < 1
Next cell
End Sub

TIA,
Anders

 

[Jacob Skaria]

Try the below. The first range is invalid "B21:26",

Sub hide_row()
For Each cell In Range("B21:B26,B30:B51,B57:B90,B95:B112")
cell.EntireRow.Hidden = cell.Value < 1
Next cell
End Sub

If this post helps click Yes

 

[Mike H]

Hi,

Neither of your examples will work, the first will build a continuous range
from B21 to b51 and the second will bomb on to many arguments. Build your
range like this. I don't understand what your actually trying to do with

cell.EntireRow.Hidden = cell.Value < 1

Perhaps you could explain a bit further.

Sub hide_row()
Dim MyRange As Range, MyRange1 As Range, MyRange2 As Range
Set MyRange = Union(Range("B21:B26"), Range("B30:B51"))
Set MyRange1 = Union(Range("B57:B90"), Range("B95:B112"))
Set MyRange2 = Union(MyRange, MyRange1)
For Each cell In MyRange2
cell.EntireRow.Hidden = cell.Value < 1
Next cell
End Sub


Mike

 

[Dave Peterson]

I'd try:
Dim Cell as range
For Each cell In Range("B21:26,B30:B51,B57:B90,B95:B112")

In fact, I would have used:
For Each cell In Range("B21:B26,B30:B51")

And I like to specify my properties--I think it serves as documentation:
For Each cell In Range("B21:26,B30:B51,B57:B90,B95:B112").Cells

 

[Anders]

Thanks Jacob.
Too many quotes I guess. Much appreciated!

 

[Chip Pearson]

Use Union to combine multiple ranges into a single range.

Dim Cell As Range
For Each Cell In Application.Union( _
Range("B21:B26"), Range("B30:B51"), _
Range("B57:B90"), Range("B95:B112"))
Cell.EntireRow.Hidden = (Cell.Value < 1)
Next Cell

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)