[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.

Output of a code selecting row number 15

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.

Output of code that selects and entire row to which a specified cell belongs.

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:

Output of code selecting a range of cells

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
Output of code selecting all rows from 5 to 8.

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:

Output of the entirerow property on a range

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
Output of entirerow code being run after selecting cells

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:

Output of selecting all rows in an active sheet

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:

Output when 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

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
Output ofcode that fills the first columns that aren't null green

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.

Leave a Reply

Your email address will not be published. Required fields are marked *