Range("a21") .end(xlDown) or .end(xlUp) . offset(1,0) . value

[JDB]

Can anyone help explain this situation;

When I try this statement:

Range("a21").end(xlDown).offset(1,0).value = x the cursor does NOT find the end of the list in column "A" to place the value in the proper cell.

When I try this statement
Range("a21").end(xlUp).offset(1,0).value = x the cursor DOES find the end of the list in column "A"to place the value in the proper cell.

This puzzles me to not end.

Thanks for any enlightenment


JDB

 

[GS]

JDB wrote :

Can anyone help explain this situation;

When I try this statement:

Range("a21").end(xlDown).offset(1,0).value = x the cursor does NOT find the
end of the list in column "A" to place the value in the proper cell.

When I try this statement
Range("a21").end(xlUp).offset(1,0).value = x the cursor DOES find the end of
the list in column "A"to place the value in the proper cell.

This puzzles me to not end.

Thanks for any enlightenment


JDB

Using End basically does the same thing as using the Ctrl and Down [or
Right] arrow keys. So.., if there's any blank cells between 'A1" and
"A21" then using End(xlDown) sets the cell ref one row above the blank
cell. The correct way to find the last row in a column is to start at
the bottom (ie: Cells(ActiveSheet.Rows.Count, 1).End(xlUP), which in
your case might be "A21". Otherwise, you could get the next row by
simply using Range("A21").Offset(1) to ref cell "A22".