|
|
Referring to
Multiple Ranges in Excel
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
You 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:
|
|
|