Vba: Delete Contents From Range Very Slowly

[muhammad susanto]

hi all..

how to make this code more fast to delete / clear contents a range (C2:BU7130)
this code work but very slowly

Sub DeleteRange()
 Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
    Dim rngSourceRange As Range
        Set wkbCrntWorkBook = ActiveWorkbook
          Set wkbSourceBook = ActiveWorkbook
           With Application.InputBox(prompt:="Select range", Title:="Range", Default:="[B]C2:BU7130[/B]", Type:=8)
             .SpecialCells (xlCellTypeConstants)
            .ClearContents
            End With
                   End Sub

thank in advance

.susanto

 

[mkuzner]

Hi Muhammad.

What about this short code? Or do you want to select which range to delete when you run your macro?

Sub DeleteRange()
    Range("C2:BU7130").Select
    Selection.ClearContents
End Sub

 

[muhammad susanto]

hi mkuzne, it work for me, but why still slowly?

all rows is visible nothing hidden row​

​

​

 

[mkuzner]

Well in my test file it works instantly.
Can you upload your file somehow so we can test it in our environment?

 

[muhammad susanto]

hi, sorry about that cause i can't upload for a reason security, my file size is over 7,4 mb

 

[Michael M]

Don't select

Sub DeleteRange()
    Range("C2:BU7130").ClearContents
End Sub

 

[mkuzner]

I guess that could also be the cause of your problem...your file obviously contains a looooooot of data, judging by the size. Does it work slowly calculating anything or any funcfion or Just when running macro?

Maybe try one thing. On the tab Formulas Turn off Automatic calculation and set it to Manual?

How to Manually Calculate Only the Active Worksheet in Excel

If you have large workbooks with a lot of formulas on the worksheets, recalculating the workbooks can take a long time.

www.howtogeek.com

 

[muhammad susanto]

Don't selects

Sub DeleteRange()
    Range("C2:BU7130").ClearContents
End Sub

hi ..still slowly.

 

[Michael M]

As @mkuzner mentioned....turn off automatic calculation until all cells are cleared

 

[muhammad susanto]

hi guys.
i turn off automatic calculation then formula
Sub DeleteRange()
Range("C2:BU7130").ClearContents
End Sub
the result is good, more faster