Hi UA

Someone just asked if it possible to highlight cells with numbers in and then delete only the numbers and leave the formulas.

I know this is possible, somehow as it's probably quite a requested function with Excel, but I don't know where to look or how to begin.

I've seen his sheet and it has loads of figures all over the place, with text cells next to them, and it's going to take him quite a while to select only the cells with figures in and remove the values.

I thought the quickest way would be to create a range to cover all the cells - text and figures alike - and then have some code that only removes the figures from the cells where there are only figures.

This is where UA step in. I've searched around and found threads on deleting rows and columns but nothing on seletive deleting of entries - numbers in particular.

Any chance someone out there has a function/macro set up that they don't mind sharing with me and my boss?

Edit
Goto
Special

Then select constants

Cheers

Tried your suggestion, chose numbers only from the selction boxes, and it highlighted some of the figures...not sure why only some...and sure what to do now? Do I press delete/clear/etc

It looks like this is the area I need to look into, not sure how to use it...I'll have a look in the help files.

Not sure why this didn't select all the figures, are any of them formatted as text?

Once you have all these cells, you can then press the DEL button, this will clear all the data, but leave any formatting. Alternatively you could then use the Edit --> Clear All, if you are happy to lose formatting.



I may be way off the mark here, but it sounds like this spreadsheet is being used repeatedly to do the same calculations with a different set of variables.

In which case there may be a couple of options to explore.

1. Record yourself clearing each cell that requires it, one at a time or all at once, tidy up the code, and voila a 'Reset' function
(Guess you may have already thought of this one)

2. The spreadsheet itself may benefit from some re-arranging, placing all the required variables for user input in the same area, may make it easier for the user, and much easier to clear the figures.

Cheers again for the good advice.

The boss has now explained to me that this is a spreadsheet that has sort of grown up out of nothing and no real planning went into it. I've offered to have a look and make some amendments where necessary.

I imagine I wll do quite a lot of amending...

But still Mike,

it's weird that not all constants were selected. Try to run something like this on A COPY of your sheet.


Martin

Glad I was some help.

Good luck with the ammending!

If I can be of any further help, drop me a line.

If some of the number weren't selected, then they may be the cells that have formulas in them. Those wouldn't be selected.

To be sure, try switching to formulas view of the worksheet. While holding Ctrl key, press and release ` key. Then Edit GoTo Special... Constants Numbers.

When happy that you're not going to lose your formulas, press Delete to remove contents only or Edit Clear All to take out contents and formats.

Cheers folks

I'll get my boss to send me the sheet and start doing some testing.

Mike, I am VERY SURE that there will be a LOT of stuff to do on the sheet.

In the meantime, you're welcome. I am glad we could help.