Paste Special problem

[brumanchu]

Hello,
I have a range of 6 columns in a worksheet where two of the columns contain
a formula that returns "" if certain columns on the same row are blank in
order to keep the worksheet looking clean.

The problem I have is that I have a macro that copies this range of cells
and then will paste special>values into another sheet. When I later use the
Ctrl + Down shortcut on one of teh formula columns, it seems to include the
blank cells in the paste special range as well.

How can I keep the original sheet clean and copy the data so I can delete
the rows with a "" in that particular column?

Thanks for the help,
bruce

How can

 

[ShaneDevenshire]

Hi,

I have one solution:

Instead of the formula entering "", have it enter " ".
Then after you do the Paste Special Valued, select those cells and press
Ctrl+H and press the spacebar in the Find What box and don't enter anything
in the replaces with, and choose replace all.

 

[Dave Peterson]

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 one 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

============
Then your =counta() will return what you want. And the End|Arrow keys
(End|DownArrow and the like) will stop at the spots you expect.