Finding the last row of a worksheet 1

[jay9333]

Hello,

I'm writing a macro that collects data from one workbook (the source) and writes it row by row to another (the destination). I'd like for it to be able to be run when data already exists in the destination, and automatically find the first empty row and start appending data there. Every now and again the user will cut some data out of the destination workbook. I'd like for my macro to recognize that the cells where the data once was are now empty, and so write over them.

Currently I'm using the call:

RowToAppendTo = Cells.SpecialCells(xlCellTypeLastCell).row + 1

I then write data via statements such as:

Cells(RowToAppendTo, 2).Value = MyData

The problem is that the macro won't recognize if data has been deleted. For instance, if there is data in the destination worksheet in rows 1 through 7, then the data in rows 6 and 7 gets "cut" out of that worksheet, and then the macro is run again... it starts appending at row 8 even though row 6 is the first empty row.

How can I get the macro to recognize the cleared cells?

thank you.

jay

 

[PHV]

Feel free to take a look at the FAQ area of this forum:
Check out the FAQ area for this forum!

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jay9333]

I've tried using the code from

http://www.tek-tips.com/faqs.cfm?spid=707&sfid=2115

It doesn't seem to be working as advertised. Thanks for the reference though, PHV. Does anyone know how to do this?

Here is my code specifically.

'... above this line I'm reading all my data from the 
'source worksheet

'got this code from tek-tips, to find true last row 
Dim ro As Variant
Dim co As Variant
Dim RowToAppendTo As Long
ro = ActiveSheet.UsedRange.Rows.count
co = ActiveSheet.UsedRange.Columns.count
RowToAppendTo = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).row + 1 
' after reading sample data, write it to the next 
' blank row in the destination worksheet

'write all the values for this sample into the 
'output worksheet on the row under the last row:
Cells(RowToAppendTo, 1).Value = PROTOCOL    
Cells(RowToAppendTo, 2).Value = LABID
'etc, etc...

The macro is behaving the exact same way as before. It doesn't recognize that rows have been cut, and writes to the worksheet as if they haven't been.

Jay

 

[PHV]

Try this:
RowToAppendTo = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row + 1
or this:
RowToAppendTo = ActiveSheet.UsedRange.Rows.Count + 1

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jay9333]

Awesome, you're my here PH.

The first statement worked (RowToAppendTo = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row + 1)

The second one did not (RowToAppendTo = ActiveSheet.UsedRange.Rows.Count + 1)

Again, thanks so much, I can finally move on in this program. I wonder if I should report the FAQ as being wrong or something...

Jay

 

[PHV]

jay9333, I have to mention that the first statement was in faq707-2112
Anyway, thanks for the star.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Numbers1]

Jay:
If you use

Dim intRowRef as integer 'varible for row number

Range ("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
If ActiveCell = "" then
intRowRef = ActiveWindow.RangeSelection.Row
Rows(intRowRef).Select
' Write code to fill the empty row
End If

This will find the first empty cell under A1, move down one cell and then select that row to insert your data. You would have to write a do loop or some other type of repeating process to continue going down the cells to determine if there is data in a row or if the row is empty. Hope this helps a bit.
Numbers

 

[xlbo]

the reason UsedRange doesn't work is that I'm guessing you aren't doing a SAVE between cutting the data and then trying to append more. The usedRange property only gets reset at save time - that's why it won't work - but yes, my FAQ could probably do with some more work on it to clarify a couple of issues

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question