Excel 2010 Bible

Part VI: Programming Excel with VBA

890

Selecting to the end of a row or column

You probably are in the habit of using key combinations, such as pressing Ctrl+Shift+→ and

Ctrl+Shift+↓, to select from the active cell to the end of a row or column. When you record these

actions in Excel (using relative recording mode), you’ll find that the resulting code works as you

would expect it to.

The following VBA procedure selects the range that begins at the active cell and extends down to

the last cell in the column (or to the first empty cell, whichever comes first). When the range is

selected, you can do whatever you want with it — copy it, move it, format it, and so on.

Sub SelectDown()

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

End Sub

Notice that the Range property has two arguments. These arguments represent the upper-left and

lower-right cells in a range.

This example uses the End method of the Range object, which returns a Range object. The End

method takes one argument, which can be any of the following constants: xlUp, xlDown,

xlToLeft, or xlToRight.

On the CD
A workbook that contains this macro is available on the companion CD-ROM. The file is named
select cells.xlsm.

Selecting a row or column

The macro that follows demonstrates how to select the column of the active cell. It uses the

EntireColumn property, which returns a range that consists of a column:

Sub SelectColumn()

ActiveCell.EntireColumn.Select

End Sub

As you may suspect, an EntireRow property also is available, which returns a range that consists

of a row.

If you want to perform an operation on all cells in the selected column, you don’t need to select the

column. For example, when the following procedure is executed, all cells in the row that contains

the active cell are made bold: