Code to change a named range

[Brian]

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.

 

[Bob Phillips]

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)"

 

[joel]

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


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

 

[Brian]

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.