I have an Excel spreadsheet that was originally produced using Excel 2003 and was then moved to Excel 365 about 18mths ago. Ever since the move operations (e.g. sorting) on one particular worksheet "Genl List" have been slow. The spreadsheet used to have quite a lot of conditional formatting which I removed and replaced with VBA code and various other improvements such as not having different data types in the same column were made. I followed various recommendations to speed things up (e.g. not updating the screen while calculations were being done, using VBNullstring instead of "" etc but still I could not speed things up to the way they were under Excel 2003. It also took a long time to copy the "Genl List" worksheet to another spreadsheet and also to copy all the data on it into a new worksheet. Even typing entries into it, such as names, was slow. Then earlier this week I had a breakthrough. I added a blank worksheet, and just used the option to copy and maintain source formatting. I renamed the old worksheet "Genl List Old" and the new one "Genl List" so that macros would now work on the new worksheet. Suddenly everything was extremely fast, possibly faster than it had been under Excel 2003.
What I don't understand is what difference the copy made that allowed the functions to speed up. I had checked for formulae (which I had also replaced by VBA code) and checked for any stray conditional formatting in the original worksheet and neither were present. What changed to make things so much better i.e how/why did my actions speed things up? What did I miss that was eventually cured by my copy actions?
You can see the speed differences by renaming the two affected worksheets and just running "Alpha Sort" and "Numeric Sort" on the two worksheets. The buttons under "CC Macros" were produced using Office RibbonX Editor and I have attached a file showing the associated code. This will allow you to link buttons to macros. Some buttons will not work as I have stripped out worksheets that were not pertinent to my query.
The environment is Excel 365 running Windows 10 Home 21H1 on a laptop
