I am having real trouble with the speed of my workbook and am looking for ways/tricks of the trade to sort it out. I cannot post it unfortunately so am just looking for general speed improvement solutions really...
I have one source sheet of static data - roughly 160,000 rows worth. To analyse the data I am using two scripting dictionaries that make unique lists from some of the column values. Also I am using a large number of SUMIFS to pull out data that fulfills certain criteria between dates. However, the results of the SUMIFS are not static - the user can change the date range - so a lot of re calculation is being done.
I have tried:
- reducing the data to test - this makes little to no difference.
- changing my named ranges to dynamic lists rather than whole columns - again this made v. little differnce
- reducing the number of formulas - this brought improvement, but given the amount I have it really should not be running this slow.
The file size is only around 29,000 KB.
Any ideas would be much appreciated as it is so slow it is barely usable at present.