How to know a row or cell is blank...

[James]

I am relatively new to Macro in Excel and do not know how to create
a macro that selects a range of cells in one work book.worksheet.row
and copy it to the next workbook.worksheet.row.

I couldn't use the "record a macro" in this case because each
woorksheet may not begin (and end with) data at the same row.

To make the matter more difficult, within the same worksheet there
are times that several consecutive rows are left blank before a new
chunk of data can be found.

I tried to copy and paste these worksheets from different workbooks
into a master workbook, but it took more than 12 days for the amount of
data and workbooks available. The helpfile and Excel books do not show
much details; and may be I am so green with macro to find a solution.
I would greatly appreciate any advises. James.

[olivialorieri]

Try selecting a cell in the last block of data (I am assuming that you don't intend to keep creating blank rows at the bottom of your data) and then using the below:

Selection.End(xlDown).Select

This will select the a cell in the same column that is on the bottom row of data. Then you will simply want to offset:

ActiveCell.Offset(1,0).Select

This will select the next cell down, then you can paste the data here.

I would recommend that you record your macro first, then edit it with the above.

Cheers,

Olivia

[Markus Scheible]

Hi James,

maybe this could help you:

..Range("XXX").SpecialCells(xlCellTypeBlanks).Cells
(1).Address

gives you the address of the first empty cell within a
selected range, maybe you could start like:

fecell = .Range("A1", "A65536").SpecialCells
(xlCellTypeBlanks).Cells(1).Address

the first block of data would be:

..range("a1", fecell)

and so on...

you then can find another data block by using

secell = .Range(fecell, "a65536").SpecialCells
(xlCellTypeBlanks).Cells(1).Address

....

maybe try a little how it works best.

Best regards,

Markus


>-----Original Message-----
>I am relatively new to Macro in Excel and do not know how
to create
>a macro that selects a range of cells in one work
book.worksheet.row
>and copy it to the next workbook.worksheet.row.
>
>I couldn't use the "record a macro" in this case because
each
>woorksheet may not begin (and end with) data at the same
row.
>
>To make the matter more difficult, within the same
worksheet there
>are times that several consecutive rows are left blank
before a new
>chunk of data can be found.
>
>I tried to copy and paste these worksheets from different
workbooks
>into a master workbook, but it took more than 12 days for
the amount of
>data and workbooks available. The helpfile and Excel
books do not show
>much details; and may be I am so green with macro to find
a solution.
>I would greatly appreciate any advises. James.
>
>.
>

[James]

Thanks Markus. I have been on a temporary task and have not been back
to my desk until this morning. My whole group I have only Excel help
file installed, but not vba help file.

Are variable fecell is the first empty cell & secell is the second
empty cell?

Again, thank you & will let you know how it turns out in the next few
days...Regards, James