Macros in Excel : Selecting Multiple Ranges in Excel VBA ~ Yogesh Gupta's Excel Tips

Sunday, May 10, 2009

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

6 comments:

vysakhJuly 13, 2009 9:50 AM
Thanks for putting this types of tips...
really saves a lot of time..
ReplyDelete
ErnestoMarch 15, 2011 9:08 PM
Thank you for this help!!!!
ReplyDelete
IqbalHamzahApril 21, 2011 1:08 PM
hi, i have problem to select more 20 different range. for example. i have variable strR = "A1215:S1215,A1256:S1256,A1292:S1292,A1328:S1328,A1369:S1369,A1405:S1405,A1434:S1434,A1470:S1470,A1506:S1506,A1542:S1542,A1574:S1574,A1610:S1610,A1646:S1646,A1682:S1682,A1718:S1718,A1754:S1754,A1783:S1783,A1812:S1812,A1844:S1844,A1876:S1876,A1912:S1912,A1948:S1948" and i want to select all these range using range(strR).select but there is error. actually on that string there are 22 different range. but if i select under 20 different range there is no error. can anyone help me?
ReplyDelete
RaviMarch 17, 2012 9:28 PM
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, r As Range
Set rng = Intersect(Target, Range("a2:a" & Rows.Count))
If rng Is Nothing Then Exit Sub
For Each r In rng
If Not IsEmpty(r.Value) Then
r.Copy Destination:=Sheets("sheet2").Range("a2")
End If
Next
Set rng = Nothing
End Sub

Hello Friends, the above program i need b2:b copies to b2 in next sheet and c2:c copies to c2 etc.... can anyone help me?
ReplyDelete
OFUENT13May 19, 2012 7:57 AM
Hello, Yogesh Gupta and guys. The program of Yogesh I liked, but I want something similar.
I need use "Union" but for 2 range of 2 different worksheet.
I mean:
Set r1 = Sheets("Sheet1").Range("A1:B2")
Set r2 = Sheets("Sheet2").Range("C3:D4")
And now I need to use "Union" for those two ranges of Sheet1 and Sheet2.
ReplyDelete
OFUENT13May 19, 2012 10:12 AM
Hello, friends.

Another idea might be:
1. Copy the information from each worksheet and attach (paste) it to a third worksheet.
2. Print the third worksheet.
3. Delete the third worksheet, if possible.

Is it possible that this can be done?
ReplyDelete