Paste Special problem


B

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
 
Ad

Advertisements

S

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.
 
Ad

Advertisements

D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top