+ Reply to Thread
Results 1 to 9 of 9

Cut and Paste is VERY Slow!!!

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 365
    Posts
    226

    Cut and Paste is VERY Slow!!!

    Hi, I am running a very simple macro to rearrange data in a large worksheet (70K+ rows). Problem: When I do a simple cut and paste (of ONE CELL, to move it up ONE ROW!!) that takes about 6-7 SECONDS for each individual cut and paste!! What is going on??? I tried turning calculation to manual - no change. The cell that is being moved contains text ("yes" or "no") and it has no dependents (nor does the cell it is being pasted into). Why in the world does it take so long for a SINGLE-CELL CUT AND PASTE?
    Code is simply:
    DataSheet.Cells(FoundRow + counter, 8).Cut DataSheet.Cells(FoundRow + counter - 1, 8)
    This one line of code takes 6 or 7 seconds to execute!

  2. #2
    Forum Contributor
    Join Date
    12-08-2020
    Location
    South Korea
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: Cut and Paste is VERY Slow!!!

    try this.

    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    DataSheet.Cells(FoundRow + counter, 8).Cut DataSheet.Cells(FoundRow + counter - 1, 8)
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    etc

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False
    Last edited by chronjy; 12-10-2021 at 12:44 AM.
    Caring environment and nature. caring you.

  3. #3
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 365
    Posts
    226

    Re: Cut and Paste is VERY Slow!!!

    I already have screen updating, calculation, and display alerts disabled. There are no event macros in the subject sheet. Status bar is not displayed.
    So my 6-second cut and paste is after disabling all of these things.

  4. #4
    Forum Contributor
    Join Date
    12-08-2020
    Location
    South Korea
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: Cut and Paste is VERY Slow!!!

    this is the best result in my case.


    
    Sub fsd()
    FoundRow = 1
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.CutCopyMode = False
    old = Timer
    
    For i = 1 To 100
    
    Cells(FoundRow + i, 1).Cut Cells(FoundRow + i, 2)
    
    Next
    MsgBox Timer - old & " Sec"
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
    End Sub
    below is more faster than use "Cut" or "Copy" method.

    Sheet2.Range("B1:B10").Value= Sheet1.Range("A1:A200").Value
    Sheet1.Range("A1:A200").ClearContents
    Last edited by chronjy; 12-10-2021 at 02:16 AM.

  5. #5
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,023

    Re: Cut and Paste is VERY Slow!!!

    I suggest uploading a sample file and someone will offer a simpler solution without cut and paste...
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!

  6. #6
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Cut and Paste is VERY Slow!!!

    You may have problem with other formulas in your worksheet.

  7. #7
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 365
    Posts
    226

    Re: Cut and Paste is VERY Slow!!!

    Unfortunately (1) the cells I'm moving have specific conditional formatting that has to go with them, so I can't just assign the value to another cell, and (2) these are not contiguous cells that I'm moving; they are scattered along the sheet and I'm using the FIND method to locate (and then move) each one individually.
    Thanks for the attempts to help, I finally just bit the bullet and spent about 5 hours running the macro on successive chunks of the spreadsheet.

  8. #8
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 365
    Posts
    226

    Re: Cut and Paste is VERY Slow!!!

    I was just hoping someone who understands the working of Excel could explain why a single-cell cut and paste operation, on a cell containing a short text string, with no dependent cells, in a sheet with no event macros (or any other macro code) would take 6-7 seconds to run.

  9. #9
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,545

    Re: Cut and Paste is VERY Slow!!!

    Try copying to an adjacent cell and then clearing the source cell:
        Application.Calculation = xlCalculationManual
        Application.ScreenUpdating = False
    
        With DataSheet.Cells(FoundRow + counter, 8)
            .Copy .Offset(-1)
            .Clear
        End With
    
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
    Artik

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Does VBA slow down the regular copy and paste?
    By BenRonen in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-08-2019, 07:54 AM
  2. Copy and paste is very slow to do work
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-14-2017, 08:30 AM
  3. [SOLVED] Slow Copy and Paste
    By DukeRondo in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-27-2014, 07:40 PM
  4. [SOLVED] Slow Copy And Paste
    By Pierce Quality in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-20-2014, 05:10 PM
  5. [SOLVED] copy and paste very slow
    By Alan in forum Excel General
    Replies: 2
    Last Post: 08-05-2005, 05:05 PM
  6. paste special/values can be very slow
    By larry godfrey in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-13-2005, 01:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1