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
|
1 2 3 |
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.
|
1 2 3 |
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
|
1 2 3 |
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.
|
1 2 3 |
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.
|
1 2 3 |
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.
|
1 2 3 |
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.
|
1 2 3 4 5 6 7 8 |
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.
