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 2^nd 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 2^nd row of the range D4 to H8 is selected within the 5^th 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.