CODEWITHSUNDEEP
excelvba
Krishn
Krishn

Reputation: 863

Is it possible to select multiple declared ranges?

As above,assuming I have declared rng1, rng2 rng3 etc.is it possible to select the declared ranges in 1 selection? If so please can you provide the code?

Thanks

Upvotes: 0

Views: 53

Answers (2)

JNevill
JNevill

Reputation: 50034

This is a tricky one, but if you record a macro you will see that the format is something like:

Range("A1:A10,C1:C10").Select

To change this over to a variable (which is the only way I can figure out how to do it):

Sub test()
    Dim rng1 As Range
    Dim rng2 As Range

    Set rng1 = Sheet1.Range("A1:A10")
    Set rng2 = Sheet1.Range("C1:C10")

    Range(rng1.Address & "," & rng2.Address).Select
End Sub

UPDATED As suggested by @user-somenumber- above that apparently was able to perfectly understand your question, but was too bothered by its brevity to post anything more than a hint, the UNION method works well here:

Sub test()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range

    Set rng1 = Sheet1.Range("A1:A10")
    Set rng2 = Sheet1.Range("C1:C10")

    Union(rng1, rng2).Select
End Sub

Upvotes: 3

cherdarchuk
cherdarchuk

Reputation: 46

Assuming you have three named ranges (rnge1, rnge2, rnge3), you can select all of them in vba like this:

Range("rnge1,rnge2,rnge3").Select

Upvotes: 0

Related Questions