Using offset to select next empty cell

[NewExcelUser]

Good Day,

I am having trouble with a code that would allow me to select the next empty cell. The code is as follows:

Sheet14.Range("k1").Copy Destination:=Sheet10.Range("a1").End(xlUp).Offset(1, 0)

It works except for the fact that it starts at cell A2 instead of A1. I have searched and googled, and I know it's probably something really silly that I do not know how to do. Any suggestions?

Thanks a million.

[Leith Ross]

Hello NewExcelUser,

The Offset method takes 2 arguments Row, and Column. Your code says to move 1 row down from A1 and stay in the same column. This why you are going to A2.

The code suggests that you're appending the source range to the current destination. That would be a logical reason for finding the last row in the destination before copying the source over.

If you simply want to copy the source to range A1 then use this...

Sheet14.Range("k1").Copy Destination:=Sheet10.Range("a1")

Sincerely,
Leith Ross

[NewExcelUser]

Hi Leith,

Thanks for the insight. I do want to copy the information from one cell to the other, however, need the offset because the information in the cell to be copied changes on a daily bases and I would like to keep track of all the changes. The changes occur once a command button is pressed. Once it is pressed the information is automatically copied to the next worksheet.

The Offset method takes 2 arguments Row, and Column. Your code says to move 1 row down from A1 and stay in the same column. This why you are going to A2.

So what would be the code to allow the copied info to begin in cell a1 and work its way down?

Thank you for your help

[Leith Ross]

Hello NewExcelUser,

Use this...

Dim LastRow As Long
  LastRow = Sheet10.Cells(Rows.Count, "A").End(xlUp).Row
  If LastRow > 1 Then LastRow = LastRow + 1
  Sheet14.Range("k1").Copy Destination:=Sheet10.Cells(LastRow, "A")

Sincerely,
Leith Ross

[NewExcelUser]

Hi Leith,

Thank you for your reply.

The code copies the cell just fine, but does not offset to the next row. It just copies over the previous entry.

Thanks again.

[peejay]

the problem occurs if the column begins empty. the code will return a 1 as the initial answer and then every answer thereafter will be 1 so it never increments itself.

the following code will do as you wish but the list will start from row 2 downwards

let me know how you go

code=

Dim LastRow As Long
LastRow = Sheet10.Cells(Rows.Count, "A").End(xlUp).Row
If LastRow >= 1 Then LastRow = LastRow + 1
Sheet14.Range("k1").Copy Destination:=Sheet10.Cells(LastRow, "A")