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?

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

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

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

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").Cop y _
Destination:=rDest
Next i



In article ,
"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?

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

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?

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

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?

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

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

Code:
--------------------

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.


--
Norie
------------------------------------------------------------------------
Norie's Profile: http://www.excelforum.com/member.php...o&userid=19362
View this thread: http://www.excelforum.com/showthread...hreadid=379694