Microsoft Excel Training Videos

Referring to multiple ranges by different methods in Microsoft Excel

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; use the Areas property to refer to the group of ranges selected on an Excel worksheet.
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 video clears the contents of three ranges on Excel Sheet1.

Sub ClearRanges()
Worksheets("Sheet1").Range("a1:b4,e3:f6,c7:d10").ClearContents
End Sub

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 ClearNamed()
Range("range_a, range_b, range_c").ClearContents
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 a Range object called myMultipleRange, defines it as the Excel ranges A1:B2 and C3:D4, and then formats the combined Excel ranges as bold.
Sub MultipleRange()
Dim r1, r2, myMultipleRange As Range
Set r1 = Sheets("Sheet1").Range("A1:B2")
Set r2 = Sheets("Sheet1").Range("C3:D4")
Set myMultipleRange = Union(r1, r2)
myMultipleRange.Font.Bold = True
End Sub

Using the Areas Property. Try this out!
You can use the Areas property in Excel to refer to the selected Excel range or to the collection of Excel ranges in a multiple-area selection. The following procedure counts the areas in the selection. If there is more than one area, a warning message is displayed.
Sub FindMultiple()
If Selection.Areas.Count > 1 Then
MsgBox "Cannot do this to a multiple selection."
End If
End Sub




Referring multiple ranges in an Excel macro