Selecting random cells and storing them in range variable in excel vba

Question

Sub selecting() 
Dim r as Range
Set r = Application.Selection
MsgBox r.Cells(1,1).Address(false,false)
MsgBox r.Cells(1,2).Address(false,false)
MsgBox r.Cells(2,1).Address(false,false)
End Sub

Now the thing is, I select some random cells as irregular range in excel sheet. I get first cell address correct but the next cell address is the cell neighbor to the first one, and not the next cell that I selected.

Basically, I want to collect values from irregular ranges into an array. It would be really helpful if I get addresses of each cell selected in this irregular range.

Simply saying, what I want is if I select cells e1,g4,d7,r1,t3 I should get an array of only these 5 cells in vba program, and this array should have no access to other cells apart from those that were selected.

Acantud Acantud · Accepted Answer · 2015-03-22T18:13:47

I am a little unclear on exactly what you are attempting, but I believe this may help you understand the behavior of the script as it's running.

Sub selecting()

    Dim myArray(0 To 100, 0 To 1) As Variant

    Dim rngSelected As Range
    Set rngSelected = Application.Selection

    Dim rng As Range
    For Each rng In rngSelected

        Dim counter As Integer
        Debug.Print "The value of cell " & counter & " is " & rng.Value & _
            " , and the address is " & rng.Address(False, False)

        myArray(counter, 0) = rng.Value
        myArray(counter, 1) = rng.Address(False, False)
        counter = counter + 1

    Next rng

End Sub

Maybe you can tweak this until you get the behavior you are looking for?

Selecting random cells and storing them in range variable in excel vba

1 Answers