Results 1 to 4 of 4

Thread: Efficiency in copying/pasting multiple columns of formulas into values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2014
    Location
    England
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Efficiency in copying/pasting multiple columns of formulas into values

    Hi,

    I'm have multiple columns of long-winded nested IF statements with a few countif conditions and when I try copying/pasting as values in one attempt it is taking at least 1.5 hours for the process to be complete - this gets longer with each month due to the increased number of records.

    From experience, can anyone tell me if it is better to copy/paste columns in parts instead of doing it in one go so that going forward I can look to make the process far more efficient and have less risk of the file crashing and wasting valuable time

    Thanks!

  2. #2
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    625
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Efficiency in copying/pasting multiple columns of formulas into values

    Hi there. Have you tried turning calculation to manual, do the copy/paste values, then turn auto calculation back on.
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  3. #3
    New Member
    Join Date
    Feb 2014
    Location
    England
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Efficiency in copying/pasting multiple columns of formulas into values

    Hi,

    Yes the calculations are set to manual when I copy/paste.

    My main worksheet will have 20k+ rows and a few columns of calculations where logic is applied mostly using nested if statements with countifs

  4. #4
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    898
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Efficiency in copying/pasting multiple columns of formulas into values

    I think the problem might be because when you do a "copy" excel doesn't know what you are going to paste so it has to copy everything in the range, whic hends up being a lot of data. You can avoid this and only copy the values by using Variant arrays.
    Assuming you know the last column and the last row ( which you can work out you can try this code:
    Code:
    Dim inarray as variant
    inarray=range(cells(1,1),cells(lastrow,lastcolumn))
    range(cells(1,1),cells(lastrow,lastcolumn))=inarray
    It should be very fast
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

Some videos you may like

User Tag List

Tags for this Thread

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
  •