Problem with macro speed.

[Steven]

I have a macro in a file that usually has about 300 lines of data from Col A
- L that is in date order with a blank row between each change in a date.
The macro goes down the date column... deletes a blank row it encounters and
continues to the next blank row which is deletes and continues until it
reaches the end of the data...it then resorts the data areaon the date ...
and then goes down the date row again and inserts a a blank row when a the
date changes. I have this file at work on a Pentium 3 machine with 256 MB
Ram using Excel 2003 and it takes about 4 seconds to process. At home I
tried to run the macro and it takes about 2 minutes. At home I have a
Pentium 4 with 512 MB Ram using Excel 2002 using WindowsXP. In analysing the
macro at home it appears that as the macro is going throught the process of
deleting the blank rows and lets say it is up to the 15 blank row that it
programmed to delete the processing starts running very slow and then after
it sorts and starts inserting a row between date changes the insert process
is very slow.

As a test I manually brought the file to where all the blank rows are
deleted and then resorted the data area on the date and then saved the file
and reopened it and ran the macro which means it picked up where all it
needed to do is insert rows and it did it in 1 second. Basically telling me
the problem is after it has deleted many rows it bogs way down.

What would be causing this and is there a solution? Why would it work so
fast on my computer at work when it appears to me I have a stronger computer
at home?

Thank you for your help.

Steven

[Tim Williams]

You may have some different options set in your two Excels which might explain the difference, or it could just be that there
really is a difference between the two versions.

You might find the speed improves if you turn off screenupdating and set calculation to manual (don't forget to reset these...)
It's also much faster just to create a range containing all of the rows to delete and just delete them in one step.

If you post your code no doubt you'll get more suggestions.

Tim


"Steven" <[email protected]> wrote in message news:[email protected]...
>I have a macro in a file that usually has about 300 lines of data from Col A
> - L that is in date order with a blank row between each change in a date.
> The macro goes down the date column... deletes a blank row it encounters and
> continues to the next blank row which is deletes and continues until it
> reaches the end of the data...it then resorts the data areaon the date ...
> and then goes down the date row again and inserts a a blank row when a the
> date changes. I have this file at work on a Pentium 3 machine with 256 MB
> Ram using Excel 2003 and it takes about 4 seconds to process. At home I
> tried to run the macro and it takes about 2 minutes. At home I have a
> Pentium 4 with 512 MB Ram using Excel 2002 using WindowsXP. In analysing the
> macro at home it appears that as the macro is going throught the process of
> deleting the blank rows and lets say it is up to the 15 blank row that it
> programmed to delete the processing starts running very slow and then after
> it sorts and starts inserting a row between date changes the insert process
> is very slow.
>
> As a test I manually brought the file to where all the blank rows are
> deleted and then resorted the data area on the date and then saved the file
> and reopened it and ran the macro which means it picked up where all it
> needed to do is insert rows and it did it in 1 second. Basically telling me
> the problem is after it has deleted many rows it bogs way down.
>
> What would be causing this and is there a solution? Why would it work so
> fast on my computer at work when it appears to me I have a stronger computer
> at home?
>
> Thank you for your help.
>
> Steven
>