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 · Accepted Answer · 2015-03-22 18:13:47Z

0

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?

answered Mar 22, 2015 at 18:13

Acantud

5083 silver badges13 bronze badges

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

1 Comment

Sumit Singh Over a year ago

Once selected range has been stored in a range variable, I want to gather the addresses of the cells selected. For eg : I selected D4, F2, G7.... Then I should be able to get an array from which I can access only these selected cells and not others. While what is happening is the range variable is storing all cells starting from D4. That means, if I access range.cells(1,1).Address(false,false) it gives D4, but when I try range.cells(2,1).Address(false,false) it gives D5 although it should have given me only values that I selected. I never selected D5.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related