Code to change a named range
 

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.

Code to change a named range
 

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.

Code to change a named range
 

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

Code to change a named range
 

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.




.