This project has moved. For the latest updates, please go here.

Name of named range in formula changes when deleting rows

Feb 19, 2014 at 11:54 AM
Edited Feb 19, 2014 at 1:08 PM
I have a template with named ranges for headings i.e =Head38 where Head38 references 'Sheet1':$A$2'. The headings are in row 3.
When I insert new rows using "InsertRowsBelow(n)", the headings remain the same. However when I delete rows (.Row(n).Delete()), the formula in the heading row changes to =Head28.

The heading is in row 3 and I am deleting blank rows from row 9 to row 100. See code below
xadj = 9
Do Until xadj = 100
   If String.IsNullOrEmpty(ws.Cell(xadj, 1).Value) Then
       ws.Row(xadj).Delete()
   Else
       If InStr(ws.Cell(xadj, 1).Value.ToString(), "Total capital") <> 0 Then
           Exit Do
       End If
       xadj = (xadj + 1)
   End If
Loop
Headings in template before updates:
Image
Named Range:
Image
Headings in template after deleting rows:
Image