Create 3 Ranges in 1

[Arlen]

Hello Experts!

I have a single range B2:B50. Within this range are three contiguous ranges
that are ever-shrinking or expanding depending on user input. At one point,
the ranges might go B2:B10, B11:B40, B41:B50, but at the very next point, the
ranges might change to B2:B17, B18:B34, B35:B50. I never know.

The visible change is this: the target range will have data figures and the
other two will turn to Zeroes. I need a dynamic range formula that finds
just the non-zeroed range in the column.

Please help. You always have in the past.

Thank you for your time.

Arlen

 

[Gary''s Student]

Sub findvals()
Set r = Range("B2:B50")
Set r1 = Nothing
For Each r2 In r
If r2.Value <> 0 Then
If r1 Is Nothing Then
Set r1 = r2
Else
Set r1 = Union(r1, r2)
End If
End If
Next
MsgBox (r1.Address)
End Sub

 

[Arlen]

Gary's Student,

How can I do this as a dynamic named range instead?

Thanks for your help so far.

Arlen

 

[Gary''s Student]

Let's assume that we have already defined a Named Range called "stuff"
Let's build on the macro:

Sub findvals()
Set r = Range("B2:B50")
Set r1 = Nothing
For Each r2 In r
If r2.Value <> 0 Then
If r1 Is Nothing Then
Set r1 = r2
Else
Set r1 = Union(r1, r2)
End If
End If
Next
s = r1.Address(ReferenceStyle:=xlR1C1)
ActiveWorkbook.Names("stuff").Delete
ActiveWorkbook.Names.Add Name:="stuff", RefersToR1C1:="=Sheet1!" & s
MsgBox (s)
End Sub

So once r1 as been constructed, we first delete the old Name "stuff" and
then re-create it based upon the new r1.

 

[Arlen]

I think I understand what the code is doing, but I don't know why it's having
a problem with the Set r = Range (2nd Line).

Gary's Student, if I do this as a macro, does it have to be run manually?
Because users are going to be switching data alot, so that would be an extra
step. I tried to create a dynamic named range called "Labels" using the name
box that looks like

=OFFSET(Data!$A$2,COUNTIF(Data!$A$2:$A$43,"=0"),0,COUNTIF(Data!$A$2:$A$43,">0"))

but it's no good. Any more thoughts?