Entire Rows and Columns

 

This example teaches you how to select entire rows and columns in Excel VBA. Are you ready?

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

1. The following code line selects the entire sheet.

Cells.Select

Entire Sheet in Excel VBA

Note: because we placed our command button on the first worksheet, this code line selects the entire first sheet. To select cells on another worksheet, you have to activate this sheet first. For example, the following code lines select the entire second worksheet.

Worksheets(2).Activate

Worksheets(2).Cells.Select

2. The following code line selects the second column.

Columns(2).Select

Column

3. The following code line selects the seventh row.

Rows(7).Select

Row

4. To select multiple rows, add a code line like this:

Rows(“5:7”).Select

Multiple Rows

5. To select multiple columns, add a code line like this:

Columns(“B:E”).Select

Multiple Columns

6. Be careful not to mix up the Rows and Columns properties with the Row and Column properties. The Rows and Columns properties return a Range object. The Row and Column properties return a single value.

Code line:

MsgBox Cells(5, 2).Row

Result:

Row Property

7. Select cell D6. The following code line selects the entire row of the active cell.

ActiveCell.EntireRow.Select

EntireRow

Note: border for illustration only.

8. Select cell D6. The following code line enters the value 2 into the first cell of the column that contains the active cell.

ActiveCell.EntireColumn.Cells(1).Value = 2

EntireColumn

Note: border for illustration only.

9. Select cell D6. The following code line enters the value 3 into the first cell of the row below the row that contains the active cell.

ActiveCell.EntireRow.Offset(1, 0).Cells(1).Value = 3

EntireRow + Offset

Note: border for illustration only.