Increase speed of macro over 20 worksheets

[ChaseCarlstrom]

Hi,

I am hoping to get some advice on how to get my macro to run faster. I am using the macro listed below to .clearcontents of a cell range on multiple sheets.

Sub ClearBook()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell As Range
For Each Cell In Range("G12:AU2310")
If Not Cell.HasFormula Then
If IsNumeric(Cell.Value) Then
Cell.ClearContents
End If
End If
Next Cell
Application.Calculation = xlCalculationAutomatic
End Sub

This worked quickly on a single worksheet, but when I use:

Sheets("Total").Select
Application.Run "'W1.5 Trial v7 - CC.xls'!ClearBook"

over 20 worksheets, it takes between 6 - 10 minutes to complete.

Note: I also tried to use:Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.ClearContents, as a different way to clear cells, but it bombed.

My real question is how do I get the marco that works for me to run faster?

Thanks,

Chase

 

[VoG]

Try

Sub ClearBook()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Range("G12:AU2310").SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
Application.Calculation = xlCalculationAutomatic
End Sub

 

[ChaseCarlstrom]

Hi VoG,


Thank you!

That changed worked perfectly!

Chase