Selectively Clearing cell contents

[Guest]

I am using Excel 97, and I frequently use if statements that put a blank
(i.e. "") in the cell display when the statement is true or in some cases
when it is false. If I copy the column and paste special/values, the cells
that had the "", even though they look blank and no longer contain a formula,
they still are not completely empty in that a control down arrow goes to the
total end of the array instead of to the next "non-blank" cell. If I do a
clear contents on the "blank" cells, then control down arrow goes to the
correct, next non-blank cell. How can I automate clearing the contents of
the "blank" cells without clearing the contents of the non-blank cells I am
trying to preserve?? Is there a workshhet function that can perform an
action, like clear contents, on an adjacent cell, or does this have to be a
VB subroutine??

 

[Dave Peterson]

You can clean it up manually if you want.

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab
Toggle Transition Navigation keys on.

Then select on of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

If you need to do this lots, you can record a macro when you do it manually.

 

[Guest]

Thanks Dave, I'll try that in the morning.