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

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

3. The following code line selects the seventh row.

Rows(7).Select

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

Rows(“5:7”).Select

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

Columns(“B:E”).Select

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:

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

ActiveCell.EntireRow.Select

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

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

Note: border for illustration only..

Leave a Reply

Your email address will not be published.