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

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
--
Gary's Student


"brenty" wrote:

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

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"brenty" wrote:
I have a column of data with blank cells in between 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

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

to copydatafilterautofilterfilter out blankscopyrecord a macro if
desired.

--
Don Guillett
SalesAid Software

"brenty" wrote in message
...
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

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

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.

brenty wrote:
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


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html