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.

 

[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

 

[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