VBA Rows Property – Access Single/Multiple by Range

What is Range.Rows property

• To select a row in an Excel sheet, you may use the Range object Rows property.
• You may select one or more rows by using the Rows property.
• Different tasks can be done like the coloring of rows, borders, background, hiding/unhide rows
• Similarly, copying/pasting, deleting rows, etc.

An example of setting background/interior color of the row

In this example, a range of A1:E10 is created.

Then we used the Rows property and applied the Interior color of row number 3 in our range.

Code:

Sub row_ex()

Dim rng As Range

Set rng = Range("A1:E10")

'Setting background color of row number 3

rng.Rows(3).Interior.ColorIndex = 25

'Selecting that range to show all cells

rng.Select

End Sub

Result:

Highlighted rows represent our entire range (by using the Select method).

Changing multiple row color

See the code below to learn how we can change/apply the interior color to multiple rows in our above-created range.

We will apply color to row number 2,3 and 4 as follows:

Code:

Sub row_ex()

Dim rng As Range

Set rng = Range("A1:E10")

'Setting background color of row number 2 to 4

rng.Rows("2:4").Interior.ColorIndex = 31

'Selecting that range to show all cells

rng.Select

End Sub

Output:

Applying borders to multiple rows by Range.Rows

This example sets the border of multiple rows in the Range.

For that, we used Borders.ColorIndex property:

Code:

Sub row_ex()

Dim rng As Range

Set rng = Range("A1:E10")

'Setting Border colors of rows

rng.Rows("4:9").Borders.ColorIndex = 42

'Selecting entire range for visibility

rng.Select

End Sub

Result:

You can see the entire range is selected and row numbers 4 to 9 changed borders are also visible.

Deleting multiple rows example

We will use this sample sheet for deleting rows example:

The following example deletes 3 to 6 number rows by using the Rows property.

Code:

Sub row_ex()

'Deleting Rows from 3 to 6

Rows("3:6").Delete

End Sub

Result:

Inserting rows example

By using Insert method, you may insert one or more rows. We will add 3 rows to our above sample sheet:

Code:

Sub row_ex()

'Inserting 3 rows

Rows("4:6").Insert

End Sub

Output:

Show/Hide Rows Example

In order to show/hide specific rows, you may use the Hidden property and set its value to True/False.

True means hide the rows.

False will unhide the rows.

See the example below to hide rows in our sample sheet:

Code:

Sub row_ex()

Hiding 3 to 6 rows

Rows("3:6").Hidden = True

End Sub

Output:

You can see, rows 3 to 6 are not displayed in the above graphic.

By executing this code, rows will be visible again:

Rows("3:6").Hidden = False