2010 macro runs slowly in 2013

I use a macro to get the count for each column in a separate spreadsheet that you select before running. It takes 2-3 seconds to run in Excel 2010 however we have upgraded to Excel 2013 and it now takes over a minute to run during which time the screen constantly flickers.

I have researched this extensively and most forums suggest adding Application.ScreenUpdating = False to the start of the macro but I have already done that.

I have attached the macro plus a sample file we run it on, open them both and then run the macro and select the other spreadsheet from the drop down menu. Click 'Include count Autobase' and then run it. Any suggestions would be much appreciated as this is a well used macro in our company and the upgrade to 2013 has slowed lots of staff done.

macro_count_v3.xlsmexcel issue example.xlsx

ADMINISTRATORS PLEASE NOTE


I can download the .XLSM file but, despite several attempts, whenever I try to downolad the .XLSX file the Forum requires me to login again. I re-login successfully, but am still unable to download the .XLSX file.

Is this a problem at my end or at the Forum end? Any assistance would be most appreciated.

Regards,

Greg M

Hi there,

This was an INTERESTING problem!

I tested your application using both Office 2010 and Office 2013 and using the data file you supplied (898 columns wide)

There was a definite speed improvement when using Office 2013 (about 43 seconds) as opposed to using Office 2101 (about 60 seconds), however, as you pointed out in your post, the screen flickered continuously while Office 2013 was processing the data, so I decided to take a look at your code.

Your code uses "Activate" and "Select" operations continuously throughout the process - these are not necessary.

There are many "Copy and Paste" operations which involve entire columns - operating on an entire column (rather than on e.g. the first 1000 rows in the column) is not the most efficient way of achieving the desired result.

Also, a more efficient way to "Copy and Paste" a Range of data is to assign those data values to a Variant array variable, e.g.

Please Login or Register  to view this content.

and then to assign the values in that array variable to the required Range, e.g.

Please Login or Register  to view this content.

Anyway, after applying the above techniques to your workbook, the time taken to process your data file was about five seconds. Also, there is no screen flicker while the data are being processed - I'm not exactly sure why, but I suspect that this is because no "Activate", "Select" or "Paste" operations are involved.

I've checked the results produced by my workbook and they appear to be identical to those produced by your own workbook.

Take a look at the attached workbook and see if it does what you need.

Hope the above helps - please let me know how you get on.

Regards,

Greg M

Thank you so much for this, I was dreading having to roll back 20+ users to 2010! Your code is a bit more detailed than ours as we aren't experts on VBA but any means and have been using fairly basic code as I'm sure you saw. Odd though that it works in 2010 and not 2013 the same way.

I have another odd one between the two versions that I'll going to submit as a new post so have a look at that one if you feel like another challenge!
Thanks again

Hi again,

Many thanks for your feedback - I'm delighted that I was able to help.

I'll try to take a look at your next post also

Regards,

Greg M