Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Unanswered: Why is delete so slow

    in the MakeSheet_Click macro of the "fcast generator.xls" workbook of the attached zip file

    has this code in in

    For i = j To tmp2
    For start = 0 To UBound(str) - 2
    If str(start) = .Range("A" & i) And .Range("A" & i - 1) <> "" Then
    .Range("A" & i).EntireRow.Delete xlShiftUp
    End If
    Next
    Next


    and this command

    .Range("A" & i).EntireRow.Delete xlShiftUp

    takes upward of 3 seconds to run and with this being repeated about 80 to a 100 times means that this macro can take over 5 miutes to run and users arn't the most patient of poeple

    everywhere else in the amacro inserts and deletes happen in the blink of an eye so why does it take so long to run at this point?
    Attached Files Attached Files
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  2. #2
    Join Date
    Jul 2003
    Location
    Plainsboro, NJ
    Posts
    11

    Post Re: Why is delete so slow

    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

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i've tried that it had no notable effect

    as for the the circular references thats an unavoidable side effect of something else

    the "planning" sheet doesn't yet excist but is created in the first few lines of macro
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Jul 2003
    Location
    UK
    Posts
    1

    Re: Why is delete so slow

    Might be worth adding -

    Application.ScreenUpdating = False

    To speed things up a bit as well

    Gary

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    thats taken about 20 seconds off the total runtime
    Last edited by m.timoney; 07-16-03 at 06:33.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Jul 2003
    Location
    Plainsboro, NJ
    Posts
    11

    Post

    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

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    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
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Jul 2003
    Location
    Plainsboro, NJ
    Posts
    11
    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

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    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)
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    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
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  11. #11
    Join Date
    Jul 2003
    Location
    Plainsboro, NJ
    Posts
    11
    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

  12. #12
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    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
    Last edited by m.timoney; 07-28-03 at 07:46.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  13. #13
    Join Date
    Jul 2003
    Location
    Plainsboro, NJ
    Posts
    11
    "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

  14. #14
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    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
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •