Excel Painfully slow, how do you speed it up?

**sgrondines** · 02-03-2014, 02:35 PM

Hi Group, I've been working on a spreadsheet with a built in macro programmed in VBA. The spreadsheet has about 267,000 columns with data in it. My VBA macro runs through the columns deleting various rows of data and then removes the blank rows. This takes an incredible amount of time to do possibly because of some pasting function that occurs every time a row is deleted. Anyways, since the newer excel contains a million rows of cells and I don't know how many columns has Microsoft considered how much memory that is to process if the whole spreadsheet is used up with data and how long it would take to process it all never mind opening the program with that much data in it in the first place? Is there anyway to help my computer or the program speed up the processing? Also, would using arrays help any in processing the data in the columns more quickly?

Thanks for any input in advance.

**popipipo** · 02-03-2014, 02:40 PM

Start your macro with setting calculation om manual
And end your macro with automatic.

Sub Macro1()
Application.Calculation = xlManual
your macro
Application.Calculation = xlAutomatic
End Sub

**Scott M2** · 02-04-2014, 06:45 AM

You could also save the workbook as a .xlb (excel binary workbook) file rather than .xlm (excel macro-enabled workbook)
See this video
https://www.youtube.com/watch?v=DsJOvT0Fzi4
Here is another one with some great tips.
https://www.youtube.com/watch?v=PhbB69qOtfw

**mwinter** · 02-04-2014, 07:02 AM

I suggest adding more RAM and a faster processor, you can turn on and off what you like but at the end of the day it is the processor that makes the calculations and the ram that assists with faster operation. If your PC is low spec or not up to scratch there is not much you can do to speed it up apart from upgrading.