Part VI: Automating Excel
A workbook that contains these macros is available on this book’s website at http://www.wiley.com/go/
excel2019bible. The file is named range copy.xlsm.
Selecting to the end of a row or column
You’re probably 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.
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.
A workbook that contains this macro is available on this book’s website at http://www.wiley.com/go/
excel2019bible. 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 row or column, you don’t
need to select the row or column. For example, when the following procedure is executed,
all cells in the row that contains the active cell are made bold:
Sub MakeRowBold()
ActiveCell.EntireRow.Font.Bold = True
End Sub
