Getting multiple ranges from user selection (Excel VBA)

Question

Is it possible to get multiple ranges from the user selection. For example if the user has selected "A1:B2" then held down ctrl and selected "E1:G2" too then can i get the first range to 1 Range variable and the second one to another?

It is possible. Here on SO you are expected to give us an example of what you've already tried so that our answers will be as helpful as possible. Please show us the relevant code, and tell us what else you'd like it to do. — Doug Glancy
– Doug Glancy, Commented May 29, 2013 at 15:12
@DougGlancy i would normally say the same thing...but if think about this question, it may be very difficult for a beginner to figure this out. — user2140173
– user2140173, Commented May 29, 2013 at 15:17
The only thing i could think of is to ask the user to input the ranges in input boxes but i would like to do it with just the Selection. The final goal is to multiply two matrices from the Selection. — user2419750
– user2419750, Commented May 29, 2013 at 15:23

Kazimierz Jawor · Accepted Answer · 2013-05-29 15:17:32Z

5

I think Areas collection is what you are looking for. Here is sample for two non continuous ranges selected:

Sub testing()
    'testing area 
    Range("A1:A10,E1:E10").Select

    Dim rngFirst As Range
    Dim rngSecond As Range

    Set rngFirst = Selection.Areas(1)
    rngFirst.Select

    'if there is no other separate range within selection to avoid errors
    'which could solved possible problems this way
    On Error Resume Next
    Set rngSecond = Selection.Areas(2)
    rngSecond.Select

End Sub

answered May 29, 2013 at 15:17

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2140173 · Accepted Answer · 2013-05-29 15:15:43Z

1

Yes its possible, this is one way to do it:

Sub Selected__Ranges()
    Range("A1:B2,E1:G2").Select
    Dim rng
    Set rng = Selection
    Debug.Print rng.Address
End Sub

answered May 29, 2013 at 15:15

user2140173

Collectives™ on Stack Overflow

Getting multiple ranges from user selection (Excel VBA)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related