Return to VBA Code Examples

VBA – Select (and work with) Entire Rows & Columns

This tutorial will demonstrate how to select and work with entire rows or columns in VBA.

First we will cover how to select entire rows and columns, then we will demonstrate how to manipulate rows and columns.

Select Entire Rows or Columns

Select Single Row

You can select an entire row with the Rows Object like this:

Or you can use EntireRow along with the Range or Cells Objects:

or

You can also use the Range Object to refer specifically to a Row:

Select Single Column

Instead of the Rows Object, use the Columns Object to select columns. Here you can reference the column number 3:

or letter “C”, surrounded by quotations:

Instead of EntireRow, use EntireColumn along with the Range or Cells Objects to select entire columns:

or

You can also use the Range Object to refer specifically to a column:

Select Multiple Rows or Columns

Selecting multiple rows or columns works exactly the same when using EntireRow or EntireColumn:

or

However, when you use the Rows or Columns Objects, you must enter the row numbers or column letters in quotations:

or

Select ActiveCell Row or Column

To select the ActiveCell Row or Column, you can use one of these lines of code:

or

Select Rows and Columns on Other Worksheets

In order to select Rows or Columns on other worksheets, you must first select the worksheet.

The same goes for when selecting rows or columns in other workbooks.

Note: You must Activate the desired workbook. Unlike the Sheets Object, the Workbook Object does not have a Select Method.

Tired of Searching for VBA Code Examples? Try AutoMacro!

Is Selecting Rows and Columns Necessary?

However, it’s (almost?) never necessary to actually select Rows or Columns. You don’t need to select a Row or Column in order to interact with them. Instead, you can apply Methods or Properties directly to the Rows or Columns. The next several sections will demonstrate different Methods and Properties that can be applied.

You can use any method listed above to refer to Rows or Columns.

Methods and Properties of Rows & Columns

Delete Entire Rows or Columns

To delete rows or columns, use the Delete Method:

or:

Insert Rows or Columns

Use the Insert Method to insert rows or columns:

or:

Great Product. AutoMacro doesn't just write your code, it teaches as you go!" - Tony, UK

Learn more

Read our 900+ Reviews

Copy & Paste Entire Rows or Columns

Paste Into Existing Row or Column

When copying and pasting entire rows or columns you need to decide if you want to paste over an existing row / column or if you want to insert a new row / column to paste your data.

These first examples will copy and paste over an existing row or column:

or

Insert & Paste

These next examples will paste into a newly inserted row or column.

This will copy row 1 and insert it into row 5, shifting the existing rows down:

This will copy column C and insert it into column E, shifting the existing columns to the right:

VBA Programming | Code Generator does work for you!

Hide / Unhide Rows and Columns

To hide rows or columns set their Hidden Properties to True. Use False to hide the rows or columns:

or

Group / UnGroup Rows and Columns

If you want to Group rows (or columns) use code like this:

To remove the grouping use this code:

This will expand all “grouped” outline levels:

and this will collapse all outline levels:

Set Row Height or Column Width

To set the column width use this line of code:

To set the row height use this line of code:

Autofit Row Height / Column Width

To Autofit a column:

To Autofit a row:

VBA Programming | Code Generator does work for you!

Rows and Columns on Other Worksheets or Workbooks

To interact with rows and columns on other worksheets, you must define the Sheets Object:

Similarly, to interact with rows and columns in other workbooks, you must also define the Workbook Object:

Get Active Row or Column

To get the active row or column, you can use the Row and Column Properties of the ActiveCell Object.

or

This also works with the Range Object: