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.

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.

Why don't you simply add back the named range in the macro after you
delete the rows


ActiveWorkbook.Names.Add Name:=BOAssociate, RefersToR1C1:="=OFFSET('BO
Ref List'!$B$2,0,0,COUNTA('BO RefList'!$B$2:$B$989),1)"
ActiveWorkbook.Names.Add Name:=FOAssociate RefersToR1C1:="=OFFSET('FO
Ref List'!$B$2,0,0,COUNTA('FO RefList'!$B$2:$B$985),1)"


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195192

http://www.thecodecage.com/forumz

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.




.