VBA Hangs after ClearContents

[bsmith81]

Hi,

I have a very simple button on an excel spreadsheet designed to move a chunk of data from one area of a worksheet to another, and then clear the contents of the area where the data was before. The problem is that every time it executes, Excel doesn't respond for at least 15 seconds afterward. It's very annoying, and I wonder if someone can help me out. I've posted the code below. I'm running excel 2k7 on a Dell Precision490, so processing power isn't a problem at all. The excel freeze does not happen when i manually delete the data using the 'delete' key! Very confusing.

Application.ScreenUpdating = False


        Range("C7:P20000").Select
                Selection.Copy
        Range("R7").Select
                Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                    False, Transpose:=False
        Range("C7:I20000").Select
            Selection.ClearContents

Application.ScreenUpdating = True

[VBA Noob]

Worked ok for me. If you have lots of formulas you could turn of autocalc as well.

Sub aa()

With Application
    .ScreenUpdating = False
    .Calculation = xlManual
End With

        Range("C7:P20000").Copy
        Range("R7").PasteSpecial xlValues
        Range("C7:I20000").ClearContents

With Application
    .ScreenUpdating = True
    .Calculation = xlAutomatic
End With

VBA Noob

[bsmith81]

I copied your code and used it - but I still get the same problem. If it was the formulas then the same t hing should happen with I perform the operation manually, which doesn't happen. So I was thinking either it's some VBA thing I don't know about - or perhaps even a bad excel install.

[VBA Noob]

Is it always 20000 rows. You could make it dynamic using the below which finds the Last used row in Col C

Dim LastRow As Long
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
With Application
    .ScreenUpdating = False
    .Calculation = xlManual
End With

        Range("C7:P" & LastRow).Copy
        Range("R7").PasteSpecial xlValues
        Range("C7:I" & LastRow).ClearContents

With Application
    .ScreenUpdating = True
    .Calculation = xlAutomatic
End With

VBA Noob

[bsmith81]

Wow, I was really skeptical that it could be the formulas that were the problem. The dynamic solution solved the issue - I thought that my computer's hardware would negate that problem - it's only 20K rows.

Anyway, Thanks a bunch for your help!

-Brian