Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA lines delete... slooow (Excel2003 VBA)

    Need to delete rows in Excel (top to bottom); here's the code:

    <pre> Do Until <Left(Range("A1").Offset(15, 0) & "1234567890123", 13) = "<END OF DATA>"
    i = i + 1
    Rows("17:17").Delete Shift:=xlUp
    Loop
    </pre>



    Maybe it's not nice like this but I want to delete lines until the <End of Data> cell has reached the top. Looking again I realize more and more now this code is ugle and I'll probably do this different tomorrow, but that would be a workaround, not a solution.

    My problem is that this loop is extremely slow sometimes (I have the impression not always). If I manually step through the code it's the delete-line that is slow (literally seconds per line). The sheet has only four columns and there's nothing below the <End of Data> cell....

    * EnableEvents false does not help
    * Calculation manual does not help
    * Screenupdating off does not help

    What am I missing here??? What is making this soo slow... could it be my PC swapping out of memory? Will that cause this inside a little loop like this???

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA lines delete... slooow (Excel2003 VBA)

    Hi Erik

    A better way to delete rows is from the bottom of the dataset upwards try
    <pre>Sub DeleteRows()
    Dim r As Long
    Dim n As Long
    n = Range("A" & Rows.Count).End(xlUp).Row
    For r = n-1 To 1 Step -1
    Range("A" & r).EntireRow.Delete
    End If
    Next r
    End Sub
    </pre>


    This assumes that there is data in all the rows in Column A (n) and will delete every line leaving <END OF DATA> intact
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA lines delete... slooow (Excel2003 VBA)

    You're looking at Range("A1").Offset(15, 0) which is equivalent to cell A16. What is the formula in that cell? I assume it is a formula, for if it was a fixed value, deleting row 17 repeatedly wouldn't have any effect on the value of A16.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA lines delete... slooow (Excel2003 VBA)

    range("A1").Offset(15,0).Address = $A$17 (so this is row 17).

    I just replaced the whole loop with one statement and that works immediately. I also know that the loop wasn't logical and yes, going from bottom to top is also more logical...

    The problem remains however; why is that code sooo slow...

    (not crucial anymore but still interested to hear if anyone has a clue)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA lines delete... slooow (Excel2003 VBA)

    > range("A1").Offset(15,0).Address = $A$17

    Apparently you have a different version of the numbering system than I have.

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA lines delete... slooow (Excel2003 VBA)

    Seems strange but in the sheet cells A1 and A2 are merged; so are A3 and A4. Guess that explains the somewhat odd counting... (sorry for the confusion, Excel did that to me...) <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA lines delete... slooow (Excel2003 VBA)

    Hi Erik

    From what Hans says, if you put this piece of code into the script ( I put it at line 1)

    <pre>MsgBox Range("A1").Offset(15, 0).Address</pre>


    The result comes back as A$16$

    Lower down in the code you have:

    <pre>Rows("17:17").Delete Shift:=xlUp</pre>


    which takes you past the last row in your range and it loops.

    If you want to keep your method of "top down" deletion you could adapt your code like so

    <pre>Do Until Left(Range("A1").Offset(15, 0) & "1234567890123", 13) = "<END OF DATA>"
    i = i + 1
    Cells(i, 1).EntireRow.Delete
    Loop
    </pre>

    Jerry

Posting Permissions

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