Fastest way to clear contents
 

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

Fastest way to clear contents
 

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

--
Regards
Frank Kabel
Frankfurt, Germany


Steph wrote:
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

Fastest way to clear contents
 

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

Mike F
"Steph" wrote in message
...
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

Fastest way to clear contents
 

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
_ _
^¢¯^
--