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

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.

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:

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

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.

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.

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.

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.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.