Speed Up Macro - Using For/Next on Rows?

[Steve_Courts]

Hi,

Example is attached. It uses only a small selection of "Monthly Data" - this could get up to 50,000 rows.

I've written a macro that takes information from a long list of cost data (which gets added to each month) and prepares a summary table using SUMIF, filters/subtotals etc.

I realise that something like this could be done using pivot tables, but due to the types of end user and some other restraints, the output "Summary" is exactly what we are after.

Because of the large volume of data, the Macro is very slow - I'm 99.9% sure this is due to the SUMIF formula being used. The code is pasted below:

Please Login or Register  to view this content.

I have one idea on speeding it up but am not sure on how to execute.

The Summary sheet prepares for the possibility that all extensions could have a cost against each cost type, but this is often not the case (however, the "Summary" sheet still needs to show all cost types against each extension).

In the "Monthly Data", I can establish a column which is =Extension&Cost Type (which I've already done in the macro, its just not being used).

I'd thought of using for/next to say, for each row in "Summary" (prior to adding subtotals), check to see if Extension&Cost Type (Columns A & B) appears in the new column in Monthly Data. If it doesn't appear at all, enter zero against each month in the row, if it does appear (at least once), paste the SUMIF formula above.

Can this be achieved?

Will it speed things up (I realise the gain may be minimal) - in the example its ok, but with the full set of data (50,000 rows) it takes 5-10 minutes!?

Are there any other things in my coding that are completely inefficient (still relatively new to this)?

As an aside, is there anyway to change the protection of the "Summary" sheet at the end of the Macro so I can still expand/collapse the subtotals?

Thanks for any help!
Steve

[davesexcel]

Your first bit of code, may be shortened with this

Please Login or Register  to view this content.

Try to avoid the use of select

[Steve_Courts]

Thanks for the tip Dave, have incorporated in my code.

Any ideas on the For/Next idea with rows? (Or if something like this would even speed things up?)