Or, you can execute the statement

i = ActiveSheet.UsedRange.Rows.Count

to reset the row count after rows are deleted, and then use
SpecialCells(xlLastCell). John Walkenbach lists this tip on his
website.


Mark


Patricia Shannon wrote:
You can find the last cell as follows:
arange.SpecialCells(xlLastCell)
where arange is a range in the desired spreadsheet
eg.
ActiveCell.SpecialCells(xlLastCell)
or range("A1").SpecialCells(xlLastCell)

(In case you lose this msg, you can get the code to go to the cell that is
in the last used row and column by recording a macro where you do Ctrl+End.)

This is the cell at the intersection of the last row used and last column
used.
This cell itself might or might not be blank.

Then you can use the offset as shown by others to move down a row.

One problem to be aware of is that if you delete the contents of cells by
using the Delete or Backspace or Ctrl+X keys, you only delete the contents.
The cells will still be part of the spreadsheet. So then the last cell would
not be what you want.

You can fix this by deleting the rows and columns by using
Menu Edit/Delete
In this case, the last cell will still not be reset until you do a Save.
Sometimes a SaveAs to itself is needed.


"geebee" wrote:

hi,

I am writing code to copy data from another sheet and paste it into the
first new empty row in another worksheet. The problem is I dont know how to
go to the first available empty row in the new worksheet, to avoid
overwriting data that is already there.

Can someone help me with this?

Thanks in advance,
geebee