Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Speed, why does this take so long?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi.

    I have formulas from columns A to AX on this worksheet. I want to copy them from rows 2 to 52000. I have written a macro to do this, just an usual

    Sub copyrows

    Range("A2:AX2").copy Range("A3:AX52000")

    End sub

    but, this takes about 2 or 3 minutes.

    Similarly, I have set up a delete row macro to reverse the above

    sub deleterows()

    Range("A3:A52000").clearcontents

    End sub

    but this takes a minute or so.

    Any ways of speeding this up? It is particularly annoying since I have put these codes in the workbook open and workbookbeforesave place.

    Thanks,

    RET79

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Place this at the beginning of your procedure.

    ActiveSheet.EnableCalculation = False
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    at the end:

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    ActiveSheet.EnableCalculation = True

    Tom

    [ This Message was edited by: TsTom on 2002-04-27 05:34 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    TSTom,

    Thanks for the tip.

    So, is this the fastest it can be done now?

    RET79

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could try:

    Range("A2:A52000").FillDown

    Don't know if that is any faster or not?

    Tom

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I won't know until I get back to the office on Monday .

    But, I will try something similar soon and let you know on the board what the results of my experiments will be!

    Thanks again,

    RET79

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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