Removing blank cells from a column

[Guest]

I have a column of data with blank cells inbetween some of the information. I
would like to list this column of data on a separate worksheet, with the
blank cells removed. Each blank cell in the column, will have data in other
cells in the same row, so the whole row won't be blank.

For example:

Existing worksheet New Worksheet

a a
b
b c
c d

d

Unfortunately I have no experience of writing macros. Can anyone help at all.

Many thanks

 

[Guest]

You don't need VBA, just AutoFilter:

In an un-used column enter:

=IF(A2="",1,0) and copy down.

The turn on AutoFilter and display and copy only the 0 cells. For
AutoFilter info see:

http://www.contextures.com/xlautofilter01.html

 

[Guest]

One way if you would like it dynamic ..

Assuming source data in Sheet1 in A1 down

In Sheet2,
Put in A1: =IF(Sheet1!A1="","",ROW())
Put in B1:
=IF(ROW()>COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A,ROW())))
Select A1:B1, copy down to cover the max expected extent of source data in
Sheet1. Hide away col A. Col B will return the required results.

 

[Don Guillett]

Sub removeblankrows()' To delete rows on the source sheet.Don't save
Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
End Sub

to copy>data>filter>autofilter>filter out blanks>copy>record a macro if
desired.

 

[Debra Dalgleish]

You can use an Advanced Filter to extract the items in column A to a
different sheet. There are instructions here:

http://www.contextures.com/xladvfilter01.html#ExtractWs

Start from the sheet where you want the new list to appear. In the
Advanced Filter dialog box, select column A as the List range, and if
you want each item listed only once, check the box for Unique records only.