can i address the cells selected when holding the CTRL key?

**rny1ef** · 06-21-2011, 07:18 AM

can i address the cells selected when holding the CTRL key?

folks, here's the background:

1. say i have a column of data
2. i select 5 cells (out of 50) by holding down the CTRL key
3. i'd like to pass the cell selections to an array
4. the array would then be passed to a procedure when i hit a button, and the procedure would iterate through the array to perform some calculations on the cell values

i tried to record a macro, but it records the 'range' of each of the selected cells and doesn't expose where excel is 'holding' the multiple selections. i am guessing there is range, select and selection to be used here, but i have no clue how to do it.

yes, i'm new, so maybe this is obvious to everyone -- but i can't figure it out.

thanks for any feedback

ron

**Domski** · 06-21-2011, 07:49 AM

Something like this maybe:

Sub test()

Dim rngLoopRange As Range

For Each rngLoopRange In Selection
    rngLoopRange = rngLoopRange + 1
Next rngLoopRange

End Sub

Dom

**teylyn** · 06-21-2011, 07:55 AM

or, if you really want the cell values in an array:

Sub test()
Dim arCells As Variant
Dim cel As Range
Dim i As Long
i = 1
ReDim arCells(1 To Selection.Count)
' fill the array
For Each cel In Selection
    arCells(i) = cel.Value
    i = i + 1
Next cel

' do something with the array
For j = 1 To UBound(arCells)
    Debug.Print arCells(j)
Next j

End Sub

cheers,

**Whizbang** · 06-21-2011, 08:41 AM

@teylyn

I was going to recommend skipping the cycle through the selected range, and just do something like...

    Dim arCells As Variant

    arCells = Selection.Value

...but after testing it, I found that this only works for adjacent cells. I cannot seem to get it to work for a non-adjacent selection without doing as you have done, and cycling through each cell in the selected range.

But, in my mind, there must be a better way than looping through each value. I've noticed that you tend to offer this kind of advice, feeding a range into an array to work with rather than working directly with the range. This is obviously much faster. What is your experience with nonadjacent ranges? Is looping the only way?

**rny1ef** · 06-21-2011, 12:55 PM

guys -- DOMSKI, TEYLYN, WHIZBANG -- thanks

this stuff, once you see it, always turns out 'understated' in it's simplicity.
yet, believe me, i spent 3 days trying to find out what ballpark i was in.

i assumed if i could select multiple cells, they had to be held 'somewhere',
but, obviously,
who would think to put it out in the open under something as 'obscure' as 'selection'

(see, i was a physicist once, so i just had to phantasize, it's only the engineers that have to make stuff work -- so this making stuff work part is new to me)

thanks again - makes a lot of sense now
ron

**Domski** · 06-21-2011, 02:24 PM

It's only obvious if you know

Most of us have spent years doing things the hard way before someone pointed out the error of our ways.

For info it would also be possible to check that the selection is within a certain range so as you're not jumping through selections that you don't want to. Another lesson maybe though...

Dom

**teylyn** · 06-21-2011, 06:26 PM

@ron, glad you found something helpful.

@whizbang, I got my insights from this thread. If shg says you need to loop, then you need to loop.

http://www.excelforum.com/excel-prog...-an-array.html