Excel VBA macro taking too long to run

Question

I have the below code where I am trying to take one file, delete and the raw data in it and then saving it as a new file. Both the files are fairly large and close to about 100mb in size. Hence, the below part of the code where i am trying to copy and paste the values is taking too long. Any suggestions on how to reduce the run-time. Thanks

 DATA_COLUMNS = "A:" & getColumnLetter(wsConfig.Range("App_RawDataFile_Headings").Rows.Count)
 FORMULA_START_COLUMN = getColumnLetter(wsConfig.Range("App_RawDataFile_Headings").Rows.Count + 1)
 FORMULA_END_COLUMN = getColumnLetter(ws.Range("XFD2").End(xlToLeft).Column)


'-- clear the column of data from A to GM
ws.Range("$A$2:$" & Right(DATA_COLUMNS, 2) & ws.Rows.Count).ClearContents

DoEvents

'--get last column which contains the formulas
strLastCol = ws.Range("XFD2").End(xlToLeft).Address

'--resize the list object to the data rows only so it doesn't cause an error
'ws.ListObjects(1).Resize ws.Range("$A$1:$" & Right(DATA_COLUMNS, 2) & "$2")


'-- clear all the rows from 3 onwards
ws.Rows("3:" & ws.Rows.Count).ClearContents

DoEvents

wkbRawDataFile.Worksheets("RAW").Range("$A$2:$" & Right(DATA_COLUMNS, 2) & wkbRawDataFile.Worksheets("RAW").Range("A1").CurrentRegion.Rows.Count).Copy

ws.Range("A2").PasteSpecial xlPasteValues

'ws.Range(DATA_COLUMNS).PasteSpecial xlPasteValues

Application.CutCopyMode = False


ws.ListObjects(1).Resize ws.Range("A1").CurrentRegion


DoEvents

'-- close the old file
wkbRawDataFile.Close False

Set r = ws.Range("$" & FORMULA_START_COLUMN & "2:" & strLastCol)
r.Copy
ws.Range(FORMULA_START_COLUMN & "3:" & FORMULA_END_COLUMN & ws.Range("A1").CurrentRegion.Rows.Count).PasteSpecial xlPasteFormulas

Application.CutCopyMode = xlCopy

wkbAppOldPivot.RefreshAll

If your spreadsheet is 100mb in size, it might be time to consider moving the data to a more appropriate back-end like an actual database. — Comintern, Feb 03 '17 at 20:13
In my personal experience, any workbook > 30MB is a time bomb waiting to get corrupted. I hope you have copies. — Mathieu Guindon, Feb 03 '17 at 20:17
1) `ws.Rows("3:" & ws.Rows.Count).ClearContents` may take a lot of processing time. Define the last actual row you need cleared. 2) instead of copy | paste. Load data to be copied to array then write array to new range. 3) turn calcs to manual. — Scott Holtzman, Feb 03 '17 at 20:36
save them as `.xlsb` for faster saving and smaller file size. You can time the code to find the slowest parts https://stackoverflow.com/documentation/excel-vba/861/debugging-and-troubleshooting/6187/use-timer-to-find-bottlenecks-in-performance — Slai, Feb 04 '17 at 02:18

score -1 · Answer 1 · answered Feb 04 '17 at 02:13

create this sub:

   sub MakeItfaster()

     application.screenupdating=false
     application.calculation=xlmanual
     worksheet.displaypagebreaks=false

end sub

then call it at the top of your code and this will help.

call MakeItFaster
 DATA_COLUMNS = "A:" & getColumnLetter(wsConfig.Range("App_RawDataFile_Headings").Rows.Count)
 FORMULA_START_COLUMN = getColumnLetter(wsConfig.Range("App_RawDataFile_Headings").Rows.Count + 1)
 FORMULA_END_COLUMN = getColumnLetter(ws.Range("XFD2").End(xlToLeft).Column)


'-- clear the column of data from A to GM
ws.Range("$A$2:$" & Right(DATA_COLUMNS, 2) & ws.Rows.Count).ClearContents

DoEvents

'--get last column which contains the formulas
strLastCol = ws.Range("XFD2").End(xlToLeft).Address

'--resize the list object to the data rows only so it doesn't cause an error
'ws.ListObjects(1).Resize ws.Range("$A$1:$" & Right(DATA_COLUMNS, 2) & "$2")


'-- clear all the rows from 3 onwards
ws.Rows("3:" & ws.Rows.Count).ClearContents

DoEvents

wkbRawDataFile.Worksheets("RAW").Range("$A$2:$" & Right(DATA_COLUMNS, 2) & wkbRawDataFile.Worksheets("RAW").Range("A1").CurrentRegion.Rows.Count).Copy

ws.Range("A2").PasteSpecial xlPasteValues

'ws.Range(DATA_COLUMNS).PasteSpecial xlPasteValues

Application.CutCopyMode = False


ws.ListObjects(1).Resize ws.Range("A1").CurrentRegion


DoEvents

'-- close the old file
wkbRawDataFile.Close False

Set r = ws.Range("$" & FORMULA_START_COLUMN & "2:" & strLastCol)
r.Copy
ws.Range(FORMULA_START_COLUMN & "3:" & FORMULA_END_COLUMN & ws.Range("A1").CurrentRegion.Rows.Count).PasteSpecial xlPasteFormulas

Application.CutCopyMode = xlCopy

wkbAppOldPivot.RefreshAll

Excel VBA macro taking too long to run

1 Answers1