spot_img

From Active Cell to Last Entry

 

This example illustrates the End property of the Range object in Excel VBA. We will use this property to select the range from the Active Cell to the last entry in a column.

Situation:

Some sales figures in column A. Assume that you will be adding more sales figures over time.

From Active Cell to Last Entry in Excel VBA

Place a command button on your worksheet and add the following code lines:

1. To select the last entry in a column, simply add the following code line:

Range(“A5”).End(xlDown).Select

Note: instead of Range(“A5”), you can also use Range(“A1”), Range(“A2”), etc. This code line is equivalent to pressing the END+DOWN ARROW.

Result when you click the command button on the sheet:

Last Entry

2. To select the range from cell A5 to the last entry in the column, add the following code line:

Range(Range(“A5”), Range(“A5”).End(xlDown)).Select

Result when you click the command button on the sheet:

From Cell A5 to Last Entry

3. To select the range from the Active Cell to the last entry in the column, simply replace Range(“A5”) with ActiveCell.

Range(ActiveCell, ActiveCell.End(xlDown)).Select

Result when you select cell A2 and click the command button on the sheet:

From Active Cell to Last Entry Result

Note: you can use the constants xlUp, xlToRight and xlToLeft to move in the other directions. This way you can select a range from the Active Cell to the last entry in a row.

spot_img
Previous articleWorkbook and Worksheet Object in Excel VBA
Next articleRead Data from a Text File using Excel VBA