Fastest way to redefine named range boundaries

[Pundit]

I have a very large workbook with 11 sheets. Five of them have over 200,000 rows of data. Part of the workflow, when data is imported as a CSV, is to copy various columns from the imported CSV to a blank sheet. When I do that, I have VBA code which redefines the existing named range to expand it from 1 row to however many rows were copied over -- in this example, 200,000 rows. My problem is that the VBA code to redefine that existing named range is taking 15 seconds. And, since I use a lot of named ranges in formulas embedded on other sheets, this process can take up to 3-4 minutes just to redefine those named range boundaries.

What is the fastest way to redefine the boundaries for a named range?

 

[BSALV]

I hope that you have at least 1 column without empty cells, then you choose that column as primary named range. The others are that named range with an offset
suppose column A starts in the 3rd row is in the worst case is 10K rows long, then you can use this formula for that column. Its a good practise to exagerate a little, so you may also use 20K

=OFFSET(Blad1!$A$3;;;COUNTA(Blad1!$A$3:$A$10002);)

the other columns are on offset, so the whole range, 14 columns width is

=OFFSET(MyColumnA;;;;14)

i made an example in another post today, see Cars

(Sorry, the translator did it wrong, the semicolons had to be commas, but otherwise look in the link)

 

[Pundit]

Thanks, but that's not a VBA solution. There are two methods I have tried. Each one (below) redefines an existing named range (called "Named_Range") from being assigned to Cell $A$2 to Cells $A$2:$A$200001 (i.e., 200,000 cells).

Method 1:
With ActiveWorkbook.Names("Named_Range")
.Name = "Named_Range"
.RefersToR1C1 = "=Sheet1!R2C1:R200001C1"
End With

Method2:
With Worksheets("Sheet1")
.Range(.Cells(2, 1), .Cells(200001, 1)).Name = "Named_Range"
End With

Both of these methods take about 15 seconds to accomplish. Given that I have about 10 named ranges which need to be redefined, my code is taking almost 3 minutes to execute. I NEED something faster!

 

[BSALV]

i suppose you 'd better set the calculation to manual, adjust your 10 named range and set the calculation again to automatic.

Sub Resize_Ranges()
     t = Timer                                                  'start chrono
     Application.Calculation = xlManual                         'manual calculation

     With Worksheets("Sheet1")
          .Range(.Cells(2, 1), .Cells(200001, 1)).Name = "Named_Range1"     'resize name 1
          .Range(.Cells(2, 5), .Cells(200001, 5)).Name = "Named_Range5"
     '....

     End With

     Application.Calculation = xlAutomatic                      'automatic again

     MsgBox Timer - t
End Sub

 

[Pundit]

Thanks. I've already done that. Those timings are with Calculation set to "manual".

 

[BSALV]

no, the msgbox gives the time of execution, so in your previous situation that was almost 3 minutes.
What do you have now as answer in that msgbox ?