Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting a macro to see the next available empty cell | Excel Discussion (Misc queries) | |||
macro to look for empty cell | Excel Discussion (Misc queries) | |||
A macro to tell excel to input to next empty cell | Excel Programming | |||
Create macro to download access 2000 table to excel 2000 spreadsheet | Excel Programming | |||
Need help with empty cell in Macro | Excel Programming |