Fastest way to clear contents

  • Thread starter Thread starter Steph
  • Start date Start date
S

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
 
Or try
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6",
"Sheet7", _
"Sheet8")).Select
Cells.Select
Selection.ClearContents

Mike F
 
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
_ _
^¢¯^
--
 
Back
Top