How can I skip blank cells when copying columns?

  • Thread starter Thread starter Mary19
  • Start date Start date
M

Mary19

How can I copy an entire column of data(values) without copying the
blank cells too, so that when I paste my column into my other
worksheet, I won't have to sit there and delete the blank cells. Thanks
in advance. :p
 
Try Advanced Filter with comuted criteria...

Let A1:A100 house the data of interest. A1 contains a distinctly formatted
label.

Leave D1 empty.

In D2 enter:

=A2<>""

Select A1:A100.
Activate Data|Filter|Advanced Filter.
Check the box for "Copy to another location".
Enter, if needed, $A$1:$A$100 in the box for List Range.
Enter $D$1:$D$2 in the box for Criteria range.
Enter $E$1 in the box for Copy to.
Leave the box for "Unique records only" unchecked.
Click OK.
 
You could even just use Data|Autofilter.

Copy your list and paste it.
Apply Data|filter|autofilter
use the dropdown to show just the blanks
delete those visible rows.
Remove the filter.

Or do the filter on the original data
Data|filter|autofilter
but filter on non-blanks
select the range
edit|goto|special|visible cells only
paste to the new location

======
and one more

copy the range
paste it to the new location
select the new range (whole column)
edit|goto|special|blanks
Edit|delete|Shift cells up
(or entirerow--if it's just a single column, there'd be no difference)
 
Back
Top