Optimizing a "Copy" / "Insert Copied Cells" code

Question

I'm relatively new to VBA but was "recruited" by my company to help make a resource forecasting tool. I'm getting a 6 second delay from the time that I run my script to the point where it completes and I'd really like to take that down to 1 or 2 seconds if possible.

As a side note, I probably have an absurd number of "IF's", "Index/Match's", and "Indirect's" in my excel sheet, all running across a week-ending date from column K to column EY... I'm not sure if that's my problem or if it's in my code.

as an FYI, here is an example of one of the excel formulas - if it seems overloaded and I should change it feel free to let me know

=IF(INDEX(INDIRECT("$A$3:$M$"&$K$4)),MATCH($B26,INDIRECT("$K$3:$K$"&$K$4)),0),COLUMN(F26))<40000,"InputDate",INDEX(INDIRECT("$A$3:$M$"&$K$4),MATCH($B26,INDIRECT("$K$3:$K$"&$K$4)),0),COLUMN(F26))))

Here is my VBA Code working in 3 steps.
Marco4() - Inserting rows in a project description table, CopyM() - Creates a string from the project table (Client Name, Proj #, Project name) Project Summary() - Copy the last range of 7 rows and then insert them 1 row below the last row.

Sub NewProject()

Macro4
CopyM
ProjectSummary

End Sub
----------------------------------------------------------   
Sub Macro4()
'insert a new line in the project description field (top)
Range("A1").End(xlDown).End(xlDown).Offset(1).EntireRow.Insert Shift:=xlDown,      
CopyOrigin:=xlFormatFromLeftOrAbove
Range("G3").Copy Destination:=Range("H1").End(xlDown).End(xlDown)

End Sub
-----------------------------------------------------------
Sub CopyM()
'Copy range of cells in column K which correspond only to project description field

Dim rng As Range
Dim rng2 As Range

Set rng = ActiveSheet.Range("K1").End(xlDown).End(xlDown)
Set rng2 = rng.Offset(1)
rng.Copy Destination:=rng2
Application.CutCopyMode = False

End Sub
------------------------------------------------------------
Sub ProjectSummary()

Dim lastrowe As Integer
Dim lastrowb As Integer
Dim pnext As Integer

lastrowe = Range("D2000").End(xlUp).Offset(2).Row
lastrowb = Range("A2000").End(xlUp).Offset(-1).Row
Rows(lastrowb & ":" & lastrowe).Copy
pnext = Range("D2000").End(xlUp).Offset(3).Row
Rows(pnext).Insert Shift:=xlDown

End Sub

**1)** `.End(xlDown).End(xlDown)` highly unreliable, read [How to determine last used row/column](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920) **2)** if you don't care about copy/paste _formatting/formulas_ (only values), change `Range1.Copy Destination:=Range2` to `Range2.Value = Range1.Value` **3)** http://stackoverflow.com/questions/3061424/speed-up-an-excel-macro — Dmitry Pavliv, May 06 '14 at 21:54
have you tried disabling automatic calculation, and turning Application.ScreenUpdating to false before the start of the operation, then setting these back at the end of the operation? — TCC, May 06 '14 at 22:00
Thanks for your reply. 1) thanks, hadn't found that page yet 2) I need to copy the formulas and formatting 3) When i run the copy/paste changes to try and speed up the macro it ends up undoing formats so I left it as is — TobyWan, May 06 '14 at 22:02
The suggestion of assigning values rather than copying them is a good one, of course that doesn't apply to the "insert copied cells" case, which does more than just move values (it does things like automatically adjusts sizes of named ranges, changes formula references etc) — TCC, May 06 '14 at 22:04
TCC - I'm spacing out manhours and budgets for each week in a projects duration and comparing them back to the original budgets, then creating a weighted average to determine project priority. Then I'm spacing out FTEs (available manhours) across the projects based on project duration and those averages. The tool is supposed to be open for manipulation and can change each week. Can I assign values without copying formulas which reference the previous week? — TobyWan, May 06 '14 at 22:16
don't understand the situation enough to answer... but will repeat that disabling automatic calculation and screen updating is the first thing to try... very often makes a huge difference — TCC, May 06 '14 at 22:28
turning off calculation.auto and screen updating dropped it to about 4.5 seconds, which is great so thank you both for that advice. Simoco, I'm reading over the post you linked - Is it ok to use .end(xldown) when I'm grabbing content from the upper-middle as opposed to the end, or should that code be erased from my memory? — TobyWan, May 06 '14 at 22:39
`Cell.End(direction)` is the VBA equivalent of the keyboard Ctrl+Arrow. Place some values in column A with a mixture of blank cells, single cells with values and multiple cells with values. Now experiment with Ctrl+Up and Ctrl+Down. Unless you have a value in Cells(Rows.Count, "A"), Ctrl+Up from that cell will always take you to the last row in column A with a value which is why we favour it. You will find other start positions and the other direction can give unexpected values if you have blank cells. — Tony Dallimore, May 07 '14 at 20:03
Thank you for your comment Tony. I understand how .End(direction) works and I believe I'm using them correctly in my code. The xldown.xldown is being used to grab the last value in a table at the top of the page, but there is also a template being copy-inserted at the bottom, for which I'm using .end(xlup). I probably should have given a screenshot of my sheet (is that possible in SO?) before posting this question, but I really appreciate everyone who has taken the time to assist — TobyWan, May 07 '14 at 21:02
INDIRECT is a volatile function so it recalculates whenever the workbook does, even if its inputs haven't changed. I'd suggest using INDEX instead: `=IF(INDEX($A$3:INDEX($M:$M,$K$4),MATCH($B26,$K$3:INDEX($K:$K,$K$4),0),COLUMN(F26))<40000,"InputDate",INDEX($A$3:INDEX($M:$M,$K$4),MATCH($B26,$K$3:INDEX($K:$K,$K$4),0),COLUMN(F26))))` This is semi-volatile so will only recalculate when the workbook opens or when its inputs change. — Rory, May 08 '14 at 12:41
@Rory: +1 you should add that comment as an answer, using `INDEX` and `MATCH` is a good solution — Our Man in Bananas, May 27 '14 at 19:09
Did I pick an answer correctly? There is no "select an answer" prompt near any of the comments, so I just copied Rory's and placed as an answer...I'd like him to get credit though so point me in the right direction if I've done it incorrectly — TobyWan, May 27 '14 at 22:33

score 0 · Accepted Answer · answered May 27 '14 at 22:29

INDIRECT is a volatile function so it recalculates whenever the workbook does, even if its inputs haven't changed. I'd suggest using INDEX instead: =IF(INDEX($A$3:INDEX($M:$M,$K$4),MATCH($B26,$K$3:INDEX($K:$K,$K$4),0),COLUMN(F2‌6))<40000,"InputDate",INDEX($A$3:INDEX($M:$M,$K$4),MATCH($B26,$K$3:INDEX($K:$K,$K‌$4),0),COLUMN(F26)))) This is semi-volatile so will only recalculate when the workbook opens or when its inputs change. – Rory May 8 at 12:41

Optimizing a "Copy" / "Insert Copied Cells" code

1 Answers1