Macros in Excel : Selecting Multiple Ranges in Excel VBA

Selecting multiple ranges in Excel VBA helps your code to work faster. You can select multiple ranges in single line code and perform the action you want to perform.

Normally we code a range by writing it within "" as under

Range("A1:A10").Select 

However if you put , between multiple ranges within the same line of the code you can select multiple ranges as given under

Range("A1:A10,D1:D10,F1:F10").Select 

How to use Multiple Ranges in Excel VBA

Following macro code will explain you how to use multiple ranges in Excel VBA

Sub Multiple_ranges()
Range("A1:A10").Select                                                                                                       
MsgBox ("Single Range Selected")
Range("A1:A10,D1:D10,F1:F10").Select
MsgBox ("Multiple Ranges Selected")
Selection.Copy
Range("A11").Select
ActiveSheet.Paste
End Sub 

In the above macro we have selected range three different ranges

A1:A10
D1:D10
F1:F10

After selection we have copied the contents to Range A11. Another option for doing this was to copy the contents one by one for each of the range. However copy and paste is only one of the example you can use this for any desired action by you.

You can combine multiple ranges into one Range object using the Union method.

The following example creates a Range object called myMultipleRange, defines it as the ranges A1:B2 and C3:D4, and then formats the combined 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