reading an arbitrary selection into a 1D array

[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

 

[Alan Beban]

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

 

[Tom Ogilvy]

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

 

[Jamie Martin]

You appear to be asking two unrelated questions:
2) I also need to cycle through each seperate [sic] selected area.

ops, i forgut mi splcheck.

 

[Jamie Martin]

I don't have "cell" as a keyword. I need to declare it, I think . . . what
kind of object should it be?

 

[J.E. McGimpsey]

Try:

Dim cell As Range