Fastest way to clear contents

[Steph]

Hi. In my workbook I have 8 sheets that are heavily populated with formulas
going from column A through FF and about 150 rows down. All I want to do is
run a macro to clear the contents of a range on all sheets. My code works
perfectly, but it takes 6 minutes to run. I though turning off Autocalc
would help, but it really didn't do much. Why does clearing cells with
formulas in them take so long? If this range was hard coded, it takes
sedonds. Help!? Thanks.........


Sub Clear()

Dim sh As Worksheet
Dim clrarray()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

clrarray = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6",
"Sheet7", "Sheet8")

For Each sh In ActiveWorkbook.Worksheets(clrarray)
sh.Range("A5:FF" & Range("b65536").End(xlUp).Row).ClearContents
Next sh
End Sub

 

[Frank Kabel]

Hi
try
sh.Range("A5:FF200").ClearContents
is this faster?

 

[Mike Fogleman]

Or try
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6",
"Sheet7", _
"Sheet8")).Select
Cells.Select
Selection.ClearContents

Mike F

 

[Soo Cheon Jheong]

Steph,
Try:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheets("Sheet1").Select
Range("A5:FF200").Select
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", _
"Sheet5", "Sheet6", "Sheet7", "Sheet8")).Select

Selection.Clear
Range("A1").Select
Sheets("Sheet1").Select

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--