VBA range.rows

The Rows property represents all the rows on the specified worksheet or range.

We are going to use the following example.

Each cell inside the range B2 to C7 has values.

Let’s run the following code.

Delete all rows

Sub DeleteAllRows()
    Range("B2:C7").Rows.Delete
End Sub

It will leave us with a blank worksheet.

That happens because we are deleting all rows in the selected range. In this example, we would have the same effect if we used Range(“B2:C7”).Columns.Delete.

Delete the selected row

In order to delete a single row, we have to use the row number. Let’s delete the second row inside the selected range.

Sub DeleteAllRows()
    Range("B2:C7").Rows(2).Delete
End Sub

If we run the above code, we are going to get the following result.

The second row is removed from the selected range.

Delete a range of rows

You can specify a range inside a range. For example, let’s select the range B2:C7, and inside this range we are going to select rows 2, 3, and 4 and delete them. We could do this using the following lines of code:

Range("B2:C7").Rows(2).Delete
Range("B2:C7").Rows(3).Delete
Range("B2:C7").Rows(4).Delete

But it’s easier and more elegant to use the following code, where you delete these rows using a single line.

Sub DeleteSelectedRows()
    Range("B2:C7").Rows("2:4").Delete
End Sub

After you run it, Excel will remove the rows with the given numbers and you are going to get this result.

Count rows

If you run the following code, Excel will count all the rows inside the entire worksheet.

Sub CountRowsInsideWorksheet()
    MsgBox Rows.Count
End Sub

The result is 1,048,576 – the number of rows inside a worksheet.

If you want to quickly find the number of rows inside a worksheet, press Ctrl + Down Arrow. The cursor will be instantly moved to the last row.

In a similar way, you can get the number of rows inside a range.

Sub CountRowsInsideRange()
    MsgBox Range("B2:C7").Rows.Count
End Sub

The code returns 6.

Highlight even rows

In a lesson about the Range.Row property I showed how you can highlight even rows inside the selected range. This time we are going to achieve the same result using Range.Rows property.

As we know Rows property represents all rows inside the selected range. We have to use indexes to distinguish single rows.

Sub HighlightEvenRows()
    Set myRange = Range("B2:C7")
    For i = 1 To myRange.Rows.Count
        If i Mod 2 = 0 Then
            myRange.Rows(i).Interior.ThemeColor = xlThemeColorAccent6
        End If
    Next i
End Sub

Code explanation:

2. First, we assign the selected range to the myRange variable, so we don’t have to use Range(“B2:C7”) all the time.

3. The loop will go from 1 to myRange.Rows.Count, which is 6.

4. For each iteration, it will check whether there is a reminder after division by 2 (modulo). In other words, it will highlight every other row.

This is the result.

Posted in vba