MACRO WAY TOO SLOW

[MOLINAMIKE]

I have a macro that takes over 15 minutes to execute which is way too long as I have many others to run in order to complete the update of a Dashboard. Would someone take a look at the below and help me optimize this code? Thank you!!

Sub Transfer_CONFIG()
Application.ScreenUpdating = False
Sheets("Master Data").Activate
Sheets("Master Data").Range("A3:AB320000").ClearContents

'copying and pasting formulas
Sheets("Master Data").Range("N2:AB2").Copy Range("N3:AB320000")

'getting data from file
Workbooks.Open Filename:="C:\myfile.XLS"
Workbooks("myfile.xls").Worksheets("Config").Range("A2:M320000").Copy Workbooks("DATA MINNING.xlsb").Worksheets("Master Data").Range("A2:M320000")

Workbooks("myfile.XLS").Close SaveChanges:=False
Sheets("Master Data").Range("N2:AB320000").Calculate

'copy and paste values with exception of N2:AB2
Sheets("Master Data").Range("N3:AB320000").Copy
Sheets("Master Data").Range("N3").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

 

[Monty]

No surprise...t takes too long to to run....as you have specified the range of the data...make dynamic data..and also use screenupdating to make it run quickly... May be sample file help to analysis...

 

[MOLINAMIKE]

Monty,

Thanks for the response. Would you provide me with an example on how to use a dynamic range as I am not sure how?

 

[Monty]

Hello

just find first row of data and last low and copy data instead of copy data the range u specified...let me know what any questions...Happy to help you.

 

[NARAYANK991]

Hi ,

The question of dynamic ranges is applicable only if your actual data does not span 320000 rows , and you are using that range only to ensure that you include all of the data , as and when it may span that range , sometime in the future.

If your data really spans those many rows at present , then using a dynamic range is not going to change anything.

Basically , what you are doing is :

1. Clearing the contents of a large range

2. Copying formulas from a small range , and pasting them into the large range

3. Opening a file

4. Copying data from the file into another large range

5. Recalculating the worksheet

6. Replacing the formulae in the large range by their values

You can always put the following statement :

Debug.Print Time

at each stage , so that you know exactly which stage takes up most of the time.

15 minutes seems to be excessive , but it entirely depends on your formulae ; if you can upload a sample workbook with say 1000 rows of data , with all the formulae in place , it will be easier to suggest the appropriate changes.

Narayan