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