[Guide] Mastering VBA Select Rows (9 Examples)
Contents
Need to Select Data Range in Excel
In Microsoft Excel, several operations like delete, edit, type, and other formatting can be done only if we select the range of cells we need to change. There are several ways in which we select the range of cells. In this article, we will discuss selecting a specific row or row using VBA to perform any action or format them.
Select a Row if You Know its Number
Here is the code that helps select a specific row if we know the row number. Let us select row number 15 here.
Sub row_select() 'Select the row no 15 Rows(15).Select End Sub
Output:
Here I already have a “Simple Interest” sheet with some data. Row number 15 of the active sheet is selected.
Using the EntireRow Property
This is a property of a cell reference. We can use it in two ways, as explained below.
Using a Range Reference
We select the entire row to which a specified cell belongs.
Sub row_select() 'Select the rows that belongs to Cell G6 Range("G6").EntireRow.Select End Sub
Output:
Since G6 belongs to row number 6, that row is selected here.
Using a Cell Reference
Sub row_select() 'Select the rows that belongs to Cell (5,3) i.e. 5th row Cells(5, 3).EntireRow.Select End Sub
Output:
Select 1 or More Rows:
The range object can be used to select 1 or more rows too.
The code below selects all rows from 5 to 8.
Sub row_select() 'Select the rows from 5 to 8 Range("5:8").EntireRow.Select End Sub
Select All Rows That Belong to a Specific Range
Let us assume a specific range B3 to E5. Now, if we use the entire row property on this range and select, we can notice that rows 3 to 5 have been selected because the specified range belongs to these three rows.
Sub row_select() 'Select the rows that belong to a range Range("B3:E5").EntireRow.Select End Sub
Output:
Selection of the Row to Which the Active Cell Belongs
The code below is run after selecting cells (10, 3) or C10.
Sub row_select() 'Select the rows to which the active cell belongs ActiveCell.EntireRow.Select End Sub
Selecting All Rows of the Active Sheet
All rows of the active sheet (Simple interest sheet in this example) is selected.
Sub row_select() 'Select the rows of the active sheet ActiveSheet.Rows.Select End Sub
Output:
Select a Specific Row in a Range
In this example, the rows are not selected from the sheet. Instead, a specified range is assumed as a sheet, and the row number specified within the code is assumed as the row number within the specified range and selected. For example, we want to select the 2nd row of the range D4 to H8. The second row of this range is row number 5 of the spreadsheet.
Sub row_select() 'Select the row within the range Range("D4:H8").Rows(2).Select End Sub
Output:
Now, we see that the 2nd row of the range D4 to H8 is selected within the 5th row of the spreadsheet.
Color Rows That Have Data in the First Column
In this example, we will consider the first 10 rows and go through the content of the first column in each row. If the cell is not null, then we will color it green.
Sub row_select() ' Loop through the first 10 rows For i = 1 To 10 ' validate if the cell is blank If Cells(i, 1).Value <> "" Then 'select the row Rows(i).EntireRow.Select ' colour the selected row With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 5296274 .TintAndShade = 0 .PatternTintAndShade = 0 End With ' end the condition End If ' end the loop Next End Sub
The rows that do not have values in column A (only until row number 10) are colored green.
Conclusion
Row selection can be used with loops and conditions in the program logic. However, unlike a manual task, most of the tasks in VBA can also be done directly without selecting cells/rows/columns/range. So, the selection operation is not always necessary.