On 23/05/2012 20:55, MZING81 wrote:
Hi Everyone,
I have a dashboard that calls about 9 macros, it works as it should it's
just on the slow side,taking baout ten minutes. The macro does work with
about 100 sheets, merging deleting rows etc.... I have attached the code
in word document if any one can look it over give me some feedback.
Any assistance would be greatly appreciated.



ActiveWorkbook.Sheets.Select


Call MZING81
Call Removetextrow
Call removeemptycells
Call UnMerge
Call filter
Call remerge
Call Text
Call mergeallworksheets
Call Removesheets

END SUB

First you need to identify where the Macro is spending its time.
I suggest adding Debug.Print "NameOfRoutine", Time
between each call.

Next optimisation is avoid .Select and operate directly on the object.
Selecting the object is slower than direct action on the object.

Unless you are very fond of seeing how it is going wrap the entire of
the outer level with xlManualCalculation and no screenupdates. There is
otherwise a global update of everything hit between every line.

Also on XL2007 try allowing screen updates - I have known it to be
faster :( although my description would be less glacially slow.

Folding some of the early simpler operations into a single For Each WS
might help a bit and if you can try it on XL2003 I have known some
macros that are mysteriously an order of magnitude slower on XL2007.

ISTR adjusting large numbers of not simply connected RowHeight was one
of those (ie even rows to one size odd ones to another).

Also think hard about the order you do things. Simplifying the data
first and then adding any fancy filters will probably be faster.

Before you can make any progress you need to know where it is wasting
its time. Profile first and then you can spend time on the right thing.

--
Regards,
Martin Brown