The spreadsheet in question is very large (it also reported a circularity when I opened it, which may or may not be intentional or a problem).

Each time a Row is deleted every cell with a formula on that Worksheet is recalculated.

Your code appears to be applied to the "Planning" Worksheet -- which I don't see in the "fcast generator.xls" Workbook -- but I'll assume that it is as large and complicated as some of the other Worksheets in this Workbook.

In short, I think you are recalculating the Worksheet -- and possibly the entire workbook &/or possibly all open Workbooks, depending on the link dependancies -- some 80 to 100 times.

The solution:
(1) Let Application.Calculation = xlCalculationManual,
(2) Execute the row deletions as required by your macro, then
(3) Application.Calculation = xlCalculationAutomatic.

I think this should do it!

-- Mike

Might be worth adding -

Application.ScreenUpdating = False

To speed things up a bit as well

Gary

thats taken about 20 seconds off the total runtime

Ok, sorry, I should have realized that you would have tried the obvious.

I've run it a few ways and replicated everything you are describing. I think the problem is really an Excel issue not a VBA issue. You can blame Bill Gates if you want, but I think the issue has to do with how link-tables are built in Excel, which is essentially an array.

The good news is that I think there are solutions...

I tried deleting manually one of the rows that the VBA code is deleting. It took about 4 1/2 secs per deletion (My computer is a really slow Celeron, so have pity on me...) this is about the same as your 3 secs. As you noted, whether Calculation was set to manual or automatic had virtually no effect.

What's happening then is that Excel's links table for that WS is being reconstructed top to bottom every time a row is deleted. The cells are considered static in location, which makes tracking calculation dependencies fast for Excel recalculations (which are frequent).

Structural changes, like a row deletion, are relatively infrequent and take longer. Programmatically deleting hundreds of rows was not on Excel's mindset when built. And allowing VBA to do such a thing, well, makes it possible, but it doesn't mean that Excel is designed to do that well.

By the way, Links between Worksheets (WSs) are essentially a pointer/handle situation, so moving WSs around would not have as much effect. The result is that calculating a bunch of cells on the same WS should be faster than calculating the same exact set of formulas that are crossing WS boundaries. But deleting or moving a WS is less painful.

I think the key for you is that "dead data", that is, Cells with numbers & text in it, but no formulas, have no linking involved and therefore support row deletions pretty much instantly.

The rows in question, roughly 490 to 600+, are "essentially" dead data in how they are used, but they do have formulas in them pulling from values in the lower-numbered rows above. There appear to be no "direct" dependecies to these cells, but I'm guessing that the Indirect() and VLookup() functions that you have throughout the WB are accessing this area. Only you know this for sure.

What I would do, is have a macro:

(1) Copy the data to another WS, "TempWS". pasting only the values
(2) Delete all the rows as needed on this new "TempWS".
(3) "Access the adjusted data"

I put step 3 in quotes because I'm not sure what is best for you here. You may want to either:

(a) Delete all the old rows in question (in one large deletion, which is one link-table rebuild) from the "Planning" WS and then paste in the correctd values.

(b) Perhaps Range.ClearContents of this area and then pasting in the correcte values would work better. (Certainly involves no Range.Delete this way.)

(c) Or perhaps put the corrected data in the correct rows within the "Temp WS" (starting at row 490 I guess) and then have the macro do a text Find/Replace in the entire WB replacing all "Planning!" with "TempWS!". This will change all formulas that pull from the "Planning!" WS to now pull from the new, corrected data on the "TempWS".

I can't tell for sure what's easiest here becaue the WB is so complicated I think only you could judge what's best/easiest here.

I hope this helps,
Mike

oh god it's even worse than i thought the growth factor on the deletes is exponentioal by adding 10 extra products put it from 5 minutes up to over 10 hours, calcuated i gave up on it after 1 hour each delete took 60 seconds and was getting longer with each cycle

With 10 extra products, I guess Excel now has a much larger spreadsheet to have to rebuild each time a row is delete. Alhough 60 sec. per delete is atrocious.

Did you try the idea of copying values to a temp WS (perhaps on a different WB), doing your deletions there, and then passing the results back? Or is that not feasible for you?

-- Mike

Originally posted by rwcplw
With 10 extra products, I guess Excel now has a much larger spreadsheet to have to rebuild each time a row is delete. Alhough 60 sec. per delete is atrocious.

Did you try the idea of copying values to a temp WS (perhaps on a different WB), doing your deletions there, and then passing the results back? Or is that not feasible for you?

-- Mike

it didn't help the only thing that seems to resets the amount of time a delete takes is shutting down excel and starting it back up, and the size of the worksheet doesn't seem to have any effect it seems to be the number of deletes/inserts/cuts made in excel since it started up (cosecutive runs of the macro seem to start the growth factor where the last run left off dispite the fact that all of the generated sheets are put in a new workbook and saved at the end of the macro)

by the way do you think i'd have the sig i've got if i didn't blame bill gates b*st*rd company for there sheer inability to provide a working program that doesn't try to get you to sign away your soul

I hear ya, brother.

You might want to try the guys on the Extreme Visual Basic Forum. There's a lot more traffic there and I'd say that at least 4 or 5 of the guys that troll there are serous experts.

You can post at:
http://visualbasicforum.com/f78

I hope they can help...
-- Mike

right i've written my own copy, cut and delete routines that use x.formula = y.formula and x.clearcontent this has dropped the performance back down to 10 minutes, which is better than 10 hours, i'll have a look at the link you posted

"right i've written my own copy, cut and delete routines that use x.formula = y.formula..."

I think you want to Let X.Value = Y.Value, no? The whole idea is for the new area to be devoid of formulas. The link-table only needs to update formulas. Dead data (data with only constants, no formulas) I found deleted instantly in my tests.

Maybe give it a whirl...

Good luck!
Mike

no it's a summary of the above data and needs to summarise the data as it's entered not just show a row of 0's the main slow down now is the "copy-insert"'s i have to do to build the planning, forecast and budget pages