Clearing Cells

[Ed Davis]

I would like to clear the data (not the formatting) in cells in a workbook
or worksheet that have a conditional format but is not a formula. There are
about 30 ranges in each sheet and 31 sheets to the workbook. I know how to
clear the contents of a range of cells but do not know what to do to find
conditional formatting without formulas. I would like to do it to all sheets
beginning with sheet "01" and ending with sheet "31".

 

[Gord Dibben]

Which version are you running?

If 2003 or earlier you can group the sheets and F5>Special>Conditional
Formats>All>OK

This selects all cells in the grouped sheets that have any CF.

Edit>Clear Contents.

Ungroup the sheets.

I'm not conversant enough yet with 2007 to advise.


Gord Dibben MS Excel MVP

 

[Ed Davis]

Will this not also clear the formulas?

 

[Ed Davis]

Sorry I forgot to answer your question. I am using 2007

 

[Gord Dibben]

Will clear formulas if the cells contain any.

You just mentioned clearing data but leave the formatting.

Let me get this straight.....................

You want to clear data from cells that have conditional formatting, right?

But if the CF cells contain a formula(not a CF formula, but a regular
in-cell formula) do not clear formulas, right?

I think what you need to do is not bother with selecting cells with CF.

You need to F5>Special>Constants>OK

This selects cells without formulas. Clear them and your formulas and CF
will remain intact.


Gord

 

[Ed Davis]

The F5 does not work in Excel 2007.
Thanks for your help though.

 

[Gord Dibben]

F5>Special>Constants works on my 2007


Gord

 

[Ed Davis]

My mistake it did work. It deleted all my descriptions and I know why now.
Thank you for your help.

 

[Ed Davis]

I have ASAP Utilities but this is a workbook that I send to and work on in
another location and they do not own ASAP utilities.
I changed the file so that all descriptions on the sheets are now formulas
by creating a temp sheet and just linking the descriptions.
Thanks for your help.

 

[Gord Dibben]

OK Ed

I guess I should have mentioned to uncheck "Text" under Constants.


Gord

 

[Ed Davis]

I guess I had a little too much to drink I should have looked a little
closer.
Thank you very much.