Dynamic named range #REF! error after row delete

lokiluke · Oct 22, 2017

I am having issue with a dynamic named range that is changing its reference if I delete a row from it using VBA.

The Named range is:

Code:

[B]AllJobsData [/B]=OFFSET(Data!$A$7,0,0,COUNTA(Data!$C$7:$C$1048576),7)

The VBA code below is designed to go through each line of AllJobsData, bottom up, and delete certain rows. Row 7 is usually deleted.

Code:

'loops through all jobs from the bottom up and deletes the rows that arent jobs
With ThisWorkbook.Sheets("Data")
    lngRows = .Range("AllJobsData").Rows.Count + 6      '+6 to account for header
    
    For lngLoop = lngRows To 7 Step -1
        If .Cells(lngLoop, 7).Value2 = "" Then
            .Rows(lngLoop).EntireRow.Delete
        End If
    Next lngLoop
End With


ThisWorkbook.Names("AllJobsData").RefersTo = "=OFFSET(Data!$A$7,0,0,COUNTA(Data!$C$7:$C$1048576),7)"

After this snippet runs the AllJobsData range changes to =OFFSET(Data!#REF!,0,0,COUNTA(Data!$C$7:$C$1048576),7) hence the last line in the code above.

I am assuming the issue is caused by deleting the first row of the table. Is there a better way to fix the named range or prevent it from happening, other than the method i have used above?

Thanks,

Luke

lokiluke · Oct 23, 2017

keira0607 said:
 The [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF [/URL] error message is quite complicated to figure out. But this commonly appears when a formula references a cell that is not valid. This means when you delete or paste over a cell that is used in the formula.

To fix it click the Undo button on the Quick Access Toolbar or else press Ctrl +Z for Windows PC and Command + Z for Mac to recover them.

Hope this will help you.

Source: http://www.repairmsexcel.com/blog/resolve-null-ref-div0-errors-in-ms-excel

Thanks keira0607 but that wont really help in my application due to using VBA to delete the lines.

After stepping away from the worksheet for a few hours I had a thought that using a relative reference for the row in the OFFSET reference might resolve it.

AllJobsData =OFFSET(Data!$A7,0,0,COUNTA(Data!$C$7:$C$1048576),7)

this no longer returns the #REF ! error if the first row is deleted, It just now returns "random" values for the row.

Dynamic named range #REF! error after row delete

lokiluke

New Member

Excel Facts

lokiluke

New Member

Similar threads

Forum statistics

Share this page

Dynamic named range #REF! error after row delete

lokiluke

New Member

Excel Facts

lokiluke

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock