51

How can I get a range of cells selected via user mouse input for further processing using VBA?

Scott Conover
  • 1,421
  • 1
  • 14
  • 27
Talguy
  • 1,045
  • 2
  • 18
  • 27
  • Record a macro, perform your actions, stop macro and then see the generated code. – Sarfraz Nov 02 '10 at 18:12
  • 1
    Tried that but that generates a predefined range. I want a to get a range that will vary every time. – Talguy Nov 02 '10 at 18:18
  • 1
    That is because you have "Use Relative References" disabled (it's in the Developer tab in the Code group). As below answer refers to, it's the Selection object (which is a Range instance) that will get recorded. – DJ Bouche Nov 03 '10 at 00:55

3 Answers3

57

Selection is its own object within VBA. It functions much like a Range object.

Selection and Range do not share all the same properties and methods, though, so for ease of use it might make sense just to create a range and set it equal to the Selection, then you can deal with it programmatically like any other range.

Dim myRange as Range
Set myRange = Selection

For further reading, check out the MSDN article.

Michael
  • 1,646
  • 2
  • 16
  • 21
  • 2
    While selection is of type Range, what props & methods is it missing? I always cast it straight to Range type anyways but just interested. – Anonymous Type Nov 04 '10 at 23:44
40

You can loop through the Selection object to see what was selected. Here is a code snippet from Microsoft (http://msdn.microsoft.com/en-us/library/aa203726(office.11).aspx):

Sub Count_Selection()
    Dim cell As Object
    Dim count As Integer
    count = 0
    For Each cell In Selection
        count = count + 1
    Next cell
    MsgBox count & " item(s) selected"
End Sub
Miyagi Coder
  • 5,464
  • 4
  • 33
  • 42
  • Shouldn't Selection.Count suffice? I think the Count property is available on Selection object. Looping through the entire selection seems an overhead. – Ravindra Sane Nov 02 '10 at 19:38
  • 3
    He was just posting an exampel on how to use the selection object. I use the count property in my code – Talguy Nov 03 '10 at 16:47
  • still depends on what you are trying to do with the selection. I'm not sure it is entirely reliable if you were say deleting cells from a range during enumeration. Although that is outside the scope of the question. – Anonymous Type Nov 04 '10 at 23:43
  • How can I count only the visible rows? – Amandine FAURILLOU Dec 11 '15 at 15:07
  • 1
    Instead of `Dim cell As Object` it's better to use `Dim cell As Range` – Leon Rom Jan 14 '18 at 03:27
23

This depends on what you mean by "get the range of selection". If you mean getting the range address (like "A1:B1") then use the Address property of Selection object - as Michael stated Selection object is much like a Range object, so most properties and methods works on it.

Sub test()
    Dim myString As String
    myString = Selection.Address
End Sub
Peter Majko
  • 1,241
  • 11
  • 22