VBA – Select (and work with) Entire Rows & Columns
IN THIS ARTICLE
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.
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!Learn More!!
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 |
VBA Programming | Code Generator does work for you!
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 |
AutoMacro | Ultimate VBA Add-in | Click for Free Trial!
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 |
0 comentarios:
Publicar un comentario