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.
and then to assign the values in that array variable to the required Range, e.g.
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
Bookmarks