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

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

--
Gary''s Student - gsnu200799

Gary's Student,

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

Thanks for your help so far.

Arlen

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200799

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.
--
Gary''s Student - gsnu200799


"Arlen" wrote:

Gary's Student,

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

Thanks for your help so far.

Arlen

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200799

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?



"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200799


"Arlen" wrote:

Gary's Student,

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

Thanks for your help so far.

Arlen

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200799