End Property

The End property emulates the operation of Ctrl+arrow key. If you have selected a cell at the top of a column of data, Ctrl+down arrow takes you to the next item of data in the column that is before an empty cell. If there are no empty cells in the column, you go to the last data item in the column. If the cell after the selected cell is empty, you jump to the next cell with data, if there is one, or the bottom of the worksheet.

The following code refers to the last data cell at the bottom of column A if there are no empty cells between it and A1:

Range("A1").End(xlDown)

To go in other directions, you use the constants xlUp, xlToLeft, and xlToRight.

If there are gaps in the data, and you want to refer to the last cell in column A, you can start from the bottom of the worksheet and go up, as long as data does not extend as far as A1048576:

Range("A1048576").End(xlUp)

In the section on rows later in this chapter, you will see a way to avoid the A1048576 reference and generalize the preceding code for different versions of Excel.

Was this article helpful?

0 0

Post a comment