In Excel 2000, how can a macro look for the next empty cell?

I have written a macro to get data from several spreadsheets and paste it
into another. Because the size of the data is variable, I am having to leave
spaces between sections of data to ensure that it doesn't overwrite anything.
Is there a way that the macro can look for the next empty row rather than me
leaving gaps?

Hi Martin

You can use a function to do this
See
http://www.rondebruin.nl/copy3.htm#header



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Martin Hextall" <MartinHextall@discussions.microsoft.com> wrote in message
news:CDFD6C1E-AC4E-436F-98C0-D8C750C9CB69@microsoft.com...
>I have written a macro to get data from several spreadsheets and paste it
> into another. Because the size of the data is variable, I am having to leave
> spaces between sections of data to ensure that it doesn't overwrite anything.
> Is there a way that the macro can look for the next empty row rather than me
> leaving gaps?

One way:

Dim vBooks As Variant
Dim rDest As Range
Dim i As Long

vBooks = Array("Book1.xls","Book2.xls","Book3.xls")

For i = LBound(vBooks) To UBound(vBooks)
Set rDest = Workbooks("DestBook.xls").Sheets(1).Range( _
"A" & Rows.Count).End(xlUp).Offset(1, 0)
Workbooks(vBooks(i)).Sheets(1).Range("A1:J10").Copy _
Destination:=rDest
Next i



In article <CDFD6C1E-AC4E-436F-98C0-D8C750C9CB69@microsoft.com>,
"Martin Hextall" <MartinHextall@discussions.microsoft.com> wrote:

> I have written a macro to get data from several spreadsheets and paste it
> into another. Because the size of the data is variable, I am having to leave
> spaces between sections of data to ensure that it doesn't overwrite anything.
> Is there a way that the macro can look for the next empty row rather than me
> leaving gaps?

Not sure if this helps, but you could try using:

lastrow = sheets("test").range("a65536").end(xlup).row

This returns the last row from the bottom up that contains something and
records the row number.

When you put new information into the spreadsheet, start by putting it in:
data = sheets("test").cells(lastrow+1,1).value

This will work, but a little long winded. I find it easy to trace if there
is an error. Other people more knowledgable might have a shorter solution.

Ashman

"Martin Hextall" wrote:

> I have written a macro to get data from several spreadsheets and paste it
> into another. Because the size of the data is variable, I am having to leave
> spaces between sections of data to ensure that it doesn't overwrite anything.
> Is there a way that the macro can look for the next empty row rather than me
> leaving gaps?

Hi Martin

You can use end down or end up to go to the last row. With end up you
actually go to the last row in the sheet, and then end up to the last used
row.

"Martin Hextall" wrote:

> I have written a macro to get data from several spreadsheets and paste it
> into another. Because the size of the data is variable, I am having to leave
> spaces between sections of data to ensure that it doesn't overwrite anything.
> Is there a way that the macro can look for the next empty row rather than me
> leaving gaps?

To find the last row in a column you can use something like this.

Dim LastRow As Long

     LastRow= Range("A65536").End(xlUp).Row

This should return the last row of data in column A.

LastRow can then be used in the rest of your code.