Slow macro due to Copy and Paste Special

  • Hello,

    I wrote a macro which is probably horribly inefficient, but it gets the job done. The macro can take up to 30min to run... It has to go through about 650 cells look to see if there is a duplicate, if there is it highlights them with a color and copies the data and pastes it above. after all of the highlighting, another macro runs, filters for the color and deletes the entire row with that color. Because the macro takes so long, I figured that I would try to make it more efficient. I saw from a quick google search, that i should try to avoid copy and paste special as much as possible, so I got ride of the copy and paste special and replaced it with a range1.value = range2.value. I got that to work no problem, but it didn't really help performance at all. I need the cells to preserve their formatting...

    Here is the code that I wrote:

    Any tips on how to improve the code would be greatly appreciated!


  • Re: Slow macro due to Copy and Paste Special


    There could be a number of ways to do this, maybe even just using Excel depending on how your data is set up and if the solution does not have to be a macro.

    Can you upload a small sample workbook with original data and desired output?

    What version of Excel are you using?

  • Re: Slow macro due to Copy and Paste Special

    I'm not sure why you need to highlight the duplicate cells unless it's just to go back and delete them afterwards, or I'm misunderstanding your question.

    If you have no other data in column A then the best way to remove all the duplicates might be something like the following. It inserts a new column, uses AdvancedFilter to copy across just the unique values to that column then deletes the original one:

    Sub RemoveDuplicates()
        Range("B1", Range("B1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True
    End Sub
  • Re: Slow macro due to Copy and Paste Special

    Well, I was able to fix the problem myself. i added

    Application.Calculation = xlCalculationManual
        '   My code here
       Application.Calculation = xlCalculationAutomatic

    That cut the time down from 30+ minutes to just a minute.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!