I was thinking too far into it and sometimes I just draw a blank. I knew it
was obvious, but I just couldnt think of it hehe.

"Bob Phillips" wrote:


Why not just recreate it?

.Names.Add Name:="BOAssociate", RefersTo:="=OFFSET('BO Ref
List'!$B$2,0,0,COUNTA('BO Ref List'!$B$2:$B$989),1)"

--

HTH

Bob

"Brian" wrote in message
...
Hello,

I have a macro that runs and it ends up having to delete row 2 on 2
different sheets, which I do with..... which I am only showing a portion
to
make it short and easy. This causes a problem is it causes my named range
to
have a #REF where it used to say $B$2.

With .Worksheets("BO Ref List")
.Range("A1").PasteSpecial
.Columns("A:B").RemoveDuplicates Columns:=Array(1, 2),
Header:=xlYes
.Range("A2:B2").Delete Shift:=xlUp

With .Worksheets("FO Ref List")
.Range("A1").PasteSpecial
.Columns("A:B").RemoveDuplicates Columns:=Array(1, 2),
Header:=xlYes
.Range("A2:B2").Delete Shift:=xlUp

Here is the actual formula in my named range....
BOAssociate "=OFFSET('BO Ref List'!$B$2,0,0,COUNTA('BO Ref
List'!$B$2:$B$989),1)"
FOAssociate "=OFFSET('FO Ref List'!$B$2,0,0,COUNTA('FO Ref
List'!$B$2:$B$985),1)"

I have attempted using a few different things, but I am drawing a blank
when
it comes to writing it into my macro to have it change the named range
back
to the above.




.