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