Clear Cells

[DavidC]

I use the following code to clear the contents of cells that are not locked on a sheet so it can be reused easily:

Dim cl As Range
For Each cl In ActiveSheet.UsedRange
If Not cl.Locked Then cl.ClearContents
Next cl

However, it seems to take longer to run that it should. My data field is only about 25 columns by 125 rows. Is there a way to get it to go faster? Thanks.

 

[DavidC]

Just timed it, and it takes about 40 seconds. And you know how people can complain. Thanks.

 

[Aaron Blood]

It would go much faster if you limited the FOR/EACH loop so it didn't include all the formulas and blank cells in the UsedRange.

For Each cell In Cells.SpecialCells(xlConstants)
If Not cell.Locked Then cell.ClearContents
Next cell

You could take it a step further and add the extra constraint on the "SpecialCells(xlConstants, xlNumbers)" if you know you can further limit the scope by only looking for values.

 

[DavidC]

Down to 5 seconds. Thanks a lot!

 

[DavidC]

Uh oh, we got problems. Get an error that it could not be run on a protected sheet. Any way around that? Thanks.

 

[Aaron Blood]

I almost forgot...

You might also try turning off screenupdating and set calculation to manual.

So... I guess the new code might look like:

Application.ScreenUpdating = False
Application.Calculation = xlManual
For Each cell In Cells.SpecialCells(xlConstants)
If Not cell.Locked Then cell.ClearContents
Next cell
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

 

[Aaron Blood]

You should get in the habit of protecting the worksheets in code at workbook startup and use the "UserInterfaceOnly" option to allow your macros to run without sheet protection interference.

If you need an example, I've got one.

 

[DavidC]

An example would be great. You can email it to drcoon@hotmail_takethisout_.com. I.e., it is @hotmail.com. Just don't want my address to be picked up by some bot and added to a spam list. Thanks so much for your help.

 

[Barry Katcher]

Thanks, guys, I've been looking for a macro like this myself. Just a thought; this could be dangerous. How 'bout starting the macro with "ActiveWorkbook.Save" and, in the macro button, include the text "If problem occurs, close without saving and re-open."

 

[Aaron Blood]

David,

You and anyone else who may be interested can get the example file straight from my website.

Look for the "user_interface_only.zip" file in the VBA section.

Regards,
Aaron

http://www.XL-Logic.com