Setting Named Ranges via VBA

[CROY1985]

Hi all

I've run into an issue when naming ranges within a vba routine, where the time it takes gets exponentially larger as the number of rows in the data to be named is increased.

My dataset is 213 columns wide, i have a standard template which data (varying number of rows) is copied into, and then the macro names each range (1 per column to 213) based on starting at row 4 and ending on the last row of data.

The first datset is around 3000 rows and the naming process takes around 22 seconds.
When i run this on a dataset of 12000 rows i would have expected it to take around 4 times longer, but it is taking greater than 5 minutes (at which point i'm abandoning the process, )

The section of code is as below:

Worksheets("Data").ActivateApplication.Calculation = xlCalculationManual
lastcol = ActiveSheet.UsedRange.Columns.Count
lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row


For i = 1 To lastcol
If Cells(2, i) <> "" Then
ActiveWorkbook.Names.Add Name:=Cells(2, i).Value, RefersTo:=Worksheets("Data").Range(Cells(4, i), Cells(lastrow, i))
End If
Next i

Any ideas on how this could be optimised?

Thank you

 

[RoryA]

Personally I wouldn't expect it to take noticeably longer to create the names just because there are more rows of data, since all you are really doing is assigning an address. Are you sure this is the part that takes the time?

 

[CROY1985]

Hi Rory

Yes, that code is the extent of the routine. It is called it in part of a larger routine, but even running that in isolation just seems to break excel.

I am using an old version of excel (2007), perhaps this is contributing to the issue?
I might try and run it on a different machine with a different version of excel and see if it fairs any better.

 

[steve the fish]

What is the value of lastcol?

 

[CROY1985]

Steve,

213

 

[RoryA]

Assuming all those cells are on the Data sheet, is it any faster if you use:

With Worksheets("Data")
    For i = 1 To lastcol
        If .Cells(2, i) <> "" Then .Range(.Cells(4, i), .Cells(lastrow, i)).Name = .Cells(2, i).Value
    Next i
End With

 

[CROY1985]

No noticeable difference, i have discovered that its when naming certain "columns" that it slows down. For example, when naming ab4:ab12000 this causes a serious slowdown.

Interestingly, this is the first column that is subsequently referenced in a formula:

=RANK(AB4,Turnover_0)+COUNTIF(AB$4:AB4,AB4)-1

I'm just wondering if its due to having a mix of named and absolute references in the above that is perhaps causing excel to do some sort of evaluation of the formula (if not a full recalc), even though i have calculation set to manual.

 

[CROY1985]

Just wanted to update on the above issue.

Naming the ranges before populating the dependent columns with formula fixes the issue.

I believe my initial suspicion that excel is evaluating the named range section of the formula despite calculation being turned off is correct, but I've no way to check.

Anyway, all is well that ends well.