eliminating blanks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Harvey Waxman - 24 Feb 2006 02:20 GMT

Is there an easy way to copy a row or column and paste only the non-blank cells?

I tried transpose checking the "skip blanks" box to no avail.

thanks

Signature

Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Remove thefrown to email me

Reply to this Message

Mike Middleton - 24 Feb 2006 07:00 GMT

Harvey -

Maybe you could try Edit | Go To | Special and choose Constants or some of
the Formula options. Then Edit | Copy, followed by a selection and Edit |
Paste.

- Mike
www.mikemiddleton.com

> Is there an easy way to copy a row or column and paste only the non-blank
> cells?
>
> I tried transpose checking the "skip blanks" box to no avail.
>
> thanks

Reply to this Message

Harvey Waxman - 24 Feb 2006 11:09 GMT

> Maybe you could try Edit | Go To | Special and choose Constants or some of
> the Formula options. Then Edit | Copy, followed by a selection and Edit |
> Paste.

I'll try, thanks

Signature

Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Remove thefrown to email me

Reply to this Message

JE McGimpsey - 24 Feb 2006 12:53 GMT

> > Maybe you could try Edit | Go To | Special and choose Constants or some of
> > the Formula options. Then Edit | Copy, followed by a selection and Edit |
> > Paste.
>
> I'll try, thanks

Mike's suggestion works well for columns/rows with constants or formulae.

If you have a mixture of both, an alternative is to apply
Data/Autofilter to the column, select (Show nonblanks) from the
autofilter dropdown, then choose Edit/Goto/Special/Visible Cells only,
Copy, then Paste.

Reply to this Message

Harvey Waxman - 24 Feb 2006 17:09 GMT

> Mike's suggestion works well for columns/rows with constants or formulae.
>
> If you have a mixture of both, an alternative is to apply
> Data/Autofilter to the column, select (Show nonblanks) from the
> autofilter dropdown, then choose Edit/Goto/Special/Visible Cells only,
> Copy, then Paste.

Mike's suggestion worked fine. I have some simple arrows I put in the menu bar
that shift left, right etc. and this did the trick in a flash once the blanks
were selected.

The blanks were in a row so the autofilter would not have been practical but
the help and suggestions are excellent. Great group of people here.

Many thanks

Signature

Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Remove thefrown to email me

Reply to this Message

Mac Forum / Applications / Excel / February 2006

eliminating blanks