Named Range Resizing takes a long time

[Dafe]

Hi. I'm totally new here, but have used this forum in the past to help answer numerous questions. I've got an interesting problem that I'm hoping someone can help me with.

I've developed a rather large macro driven Excel tool and am now at the point where I'm trying to optimize some of my somewhat poorly written code. While time-testing one of my macros, I noticed that this particular line of code is particularly time consuming. It takes 2.5 s on my computer just to run this single line. If I remove the "+ 1" from the r.Rows.Count then the time drops to 0.05 s. Is this a really inefficient way of adding a row to a named range? Can anyone suggest a faster way to accomplish the same thing?

Names("DevelopmentPlan").RefersTo = "=" & r.Resize(r.Rows.Count + 1, r.Columns.Count).Address 

Where, r = Range("DevelopmentPlan")

[Norie]

How large is the original range?

I there a lot of formatting or formulas in it?

[Dafe]

It's not particularly big. Maybe 10 rows and 5 columns. It is formatted though - the code is actually used in a macro that allows the user to add a row to the table while still preserving the formatting and formulas.

[nilem]

maybe so

With Range("DevelopmentPlan")
    .Resize(.Rows.Count + 1).Name = "DevelopmentPlan"
End With

for example

Sub ert()
With Range("DevelopmentPlan")
    .Resize(.Rows.Count + 1).Name = "DevelopmentPlan"
End With
With ActiveWorkbook.Names("DevelopmentPlan")
    .RefersToRange.Select
    MsgBox .RefersTo
End With
End Sub

[Dafe]

Nilem, I tried your code and it seemed to take about the same amount of time. It is a much cleaner way of writing it though, thank you for that.

Perhaps the code isn't the problem. Maybe instead it's the repercussions of the change. I do have several large data tables that reference the named range "Development Plan". I have the spreadsheet set to manual calculation while the macro runs, but perhaps that's not enough. I guess my understanding of Excel is not great enough to understand the full impact of some of these changes.

[Norie]

You could try turning off screen updating and setting calculation to manual in the code.

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

' code to resize range

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

[Dafe]

Thanks Norie. I already had both of those turned off.

I was just doing some more experimenting and discovered that the lag exists even when I manually rename the range from within Excel. When I go to Formulas > Name Manager and resize the range, I get about a 2-3 second delay while Excel grinds away making the change. This does not happen with most of my other named ranges, so it must have to do with the 1000+ cells that are referencing this range elsewhere in the tool. Presumably they must still have to update - even with the spreadsheet set to calculate manually.

It sounds like I should be looking to those cells for a better way to calculate/reference, rather than the macro - which seems to be fine.

Thanks for your help, everyone. I'll report back with any new findings.