Refer to Multiple Ranges Excel Worksheet

Referring to multiple ranges in an Excel Worksheet using a macro

Using the appropriate method, you can easily refer to multiple ranges in Microsoft Excel. Use the Range and Union methods to refer to any group of Excel ranges. Using the Range Property  we can refer to multiple Excel ranges with the Range property by putting commas between two or more references. The following example shown in the Excel training video fills s the contents of three ranges on Excel Sheet1 with red colour, makes the font face white and finally the font face bold.

Sub accessmultipleranges()

Worksheets(“Sheet1”).Range(“A2:A4,D2:D4,H2:H4”).Interior.ColorIndex = 3

Worksheets(“Sheet1”).Range(“A2:A4,D2:D4,H2:H4”).Font.ColorIndex = 2

Worksheets(“Sheet1”).Range(“A2:A4,D2:D4,H2:H4”).Font.Bold = True

End Sub

Note: Macro actions cannot be undone!

Excel Named ranges make using the Range property to work with multiple ranges easier. The following example also shown in the Excel training video works when all three named ranges are on the same Excel sheet.

Sub accessnamedranges()

Range(“students,courses,startdates”).Font.Italic = True

End Sub

Using the Union Method. Try it out!  You can combine multiple Excel ranges into one Range object using the Union method. The following example creates Range objects called r1,r2,r3 and multirange, defines it as the Excel ranges A2:A4,D2:D4 and H2:H4, and then underlines the combined Excel ranges.

Sub accessrangesvariables()

Dim r1, r2, r3, multirange

Set r1 = Sheets(“Sheet1”).Range(“A2:A4”)

Set r2 = Sheets(“Sheet1”).Range(“D2:D4”)

Set r3 = Sheets(“Sheet1”).Range(“H2:H4”)

Set multirange = Union(r1, r2, r3)  multirange.Font.Underline = True

End Sub

Watch the Excel training video below to see how we refer to the multiple ranges in an Excel worksheet using VBA:

Further reading:

Macros in Excel : Selecting Multiple Ranges in Excel VBA

Leave a Reply

Your email address will not be published. Required fields are marked *