Speedup Slow workbooks: PasteDown As Value

When your workbook becomes huge and slow, one may sometimes be tempted to set the Excel calculation from the default auto to normal (which is not a good practice) out of frustration. The likely culprits slowing the program down are Volatile Functions 

To counter this issue, you may want to try adopting  "PASTEDOWN as Value" method which I borrowed from production-scheduling.com

How it works: For columns filled with formulas, only the TOP cell retains the formula, the rest of the cells below are pulled down as formulas and converted straight to values through this macro. Select the Top cell with formula and and trigger the macro ~ the macro will "fill down" cells below accordingly (auto-converting the formula to value) as long as the column directly to the left is not empty. 

PS: As this is a utility I used rather frequently, I have added it to my ribbon as per below.

The Coding I use for this PasteDown Utility is:

PASTE DOWN AS VALUE MACRO

Sub PasteDownAsValue()

On Error Resume Next

If ActiveCell.HasArray = True Or ActiveCell.HasFormula = True Then

ActiveCell.Copy Range(ActiveCell.Offset(1, 0).Address & ":" & Left(ActiveCell.Address, 2) & Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Row)

Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(1, 0).End(xlDown)).Value = Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(1, 0).End(xlDown)).Value

End If

End Sub

Rationale: it reduces recalculation triggered by formulas  involving volatile functions. The formula at the top is retained as a trail for one to trace back how the values are derived for the entire column.

Hope this helps in speeding up your spreadsheet.