reading an arbitrary selection into a 1D array

  • Thread starter Thread starter Jamie Martin
  • Start date Start date
J

Jamie Martin

Hi,

I want to let my user select any combination of cells, and then read the
values from them into a one-dimensional array. How can I do this? Some parts
of the selection may be two-dimensional, while other parts may be
one-dimensional. I also need to cycle through each seperate selected area.

Thanks for any help,

Jamie
 
You appear to be asking two unrelated questions:

1) I want to let my user select any combination of cells, and then read
the values from them into a one-dimensional array. How can I do this?

2) I also need to cycle through each seperate [sic] selected area.

1) If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook:

Sub test1()
With Selection
For i = 1 To .Areas.Count
arr = MakeArray(arr, .Areas(i), 1)
Next
End With
arr = SubArray(arr, 2, UBound(arr), 1, 1)
End Sub

2) Sub test2()
For Each iArea In Selection.Areas
For Each iCell In iArea
Debug.Print iCell.Value
Next
Next
End Sub

Alan Beban
 
Another way might be

Dim myArray() as Variant
Dim rng as Range
Dim i as long
set rng = Selection
Redim myArray(1 to rng.Count)
i = 0
for each cell in rng
i = i + 1
myArray(i) = cell.Value
Next

so as a test I filled the sheet so each cell displayed its address, selected
some cells at random, then ran this:

Sub Tester1()
Dim myArray() As Variant
Dim rng As Range
Dim i As Long
Set rng = Selection
Debug.Print rng.Address
ReDim myArray(1 To rng.Count)
i = 0
For Each cell In rng
i = i + 1
myArray(i) = cell.Value
Debug.Print i, myArray(i)
Next


End Sub


Which produced:
$A$1,$D$8:$E$9,$C$3,$E$1,$C$13:$D$14
1 A1
2 D8
3 E8
4 D9
5 E9
6 C3
7 E1
8 C13
9 D13
10 C14
11 D14
 
Alan Beban said:
You appear to be asking two unrelated questions:
2) I also need to cycle through each seperate [sic] selected area.
Click to expand...

ops, i forgut mi splcheck.
 
I don't have "cell" as a keyword. I need to declare it, I think . . . what
kind of object should it be?
 
Back
Top