VBA - Select (and Work With) Entire Rows & Columns - Automate Excel

In this Article

• Select Entire Rows or Columns
  • Select Single Row
  • Select Single Column
  • Select Multiple Rows or Columns
  • Select ActiveCell Row or Column
  • Select Rows and Columns on Other Worksheets
  • Is Selecting Rows and Columns Necessary?
• Methods and Properties of Rows & Columns
  • Delete Entire Rows or Columns
  • Insert Rows or Columns
  • Copy & Paste Entire Rows or Columns
  • Hide / Unhide Rows and Columns
  • Group / UnGroup Rows and Columns
  • Set Row Height or Column Width
  • Autofit Row Height / Column Width
• Rows and Columns on Other Worksheets or Workbooks
• Get Active Row or Column

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:

Rows(5).Select

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

Range("B5").EntireRow.Select

or

Cells(5,1).EntireRow.Select

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

Range("5:5").Select

Select Single Column

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

Columns(3).Select

or letter “C”, surrounded by quotations:

Columns("C").Select

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

Range("C5").EntireColumn.Select

or

Cells(5,3).EntireColumn.Select

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

Range("B:B").Select

Select Multiple Rows or Columns

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

Range("B5:D10").EntireRow.Select

or

Range("B5:B10").EntireColumn.Select

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

Rows("1:3").Select

or

Columns("B:C").Select

Select ActiveCell Row or Column

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

ActiveCell.EntireRow.Select

or

ActiveCell.EntireColumn.Select

Select Rows and Columns on Other Worksheets

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

Sheets("Sheet2").Select Rows(3).Select

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

Workbooks("Book6.xlsm").Activate Sheets("Sheet2").Select Rows(3).Select

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

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:

Rows("1:4").Delete

or:

Columns("A:D").Delete

Insert Rows or Columns

Use the Insert Method to insert rows or columns:

Rows("1:4").Insert

or:

Columns("A:D").Insert

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:

Range("1:1").Copy Range("5:5")

or

Range("C:C").Copy Range("E:E")

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:

Range("1:1").Copy Range("5:5").Insert

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

Range("C:C").Copy Range("E:E").Insert

Hide / Unhide Rows and Columns

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

'Hide Rows Rows("2:3").EntireRow.Hidden = True 'Unhide Rows Rows("2:3").EntireRow.Hidden = False

or

'Hide Columns Columns("B:C").EntireColumn.Hidden = True 'Unhide Columns Columns("B:C").EntireColumn.Hidden = False

Group / UnGroup Rows and Columns

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

'Group Rows Rows("3:5").Group 'Group Columns Columns("C:D").Group

To remove the grouping use this code:

'Ungroup Rows Rows("3:5").Ungroup 'Ungroup Columns Columns("C:D").Ungroup

This will expand all “grouped” outline levels:

ActiveSheet.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8

and this will collapse all outline levels:

ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1

Set Row Height or Column Width

To set the column width use this line of code:

Columns("A:E").ColumnWidth = 30

To set the row height use this line of code:

Rows("1:1").RowHeight = 30

Autofit Row Height / Column Width

To Autofit a column:

Columns("A:B").Autofit

To Autofit a row:

Rows("1:2").Autofit

Rows and Columns on Other Worksheets or Workbooks

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

Sheets("Sheet2").Rows(3).Insert

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

Workbooks("book1.xlsm").Sheets("Sheet2").Rows(3).Insert

Get Active Row or Column

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

MsgBox ActiveCell.Row

or

MsgBox ActiveCell.Column

This also works with the Range Object:

MsgBox Range("B3").Column