A more elegant solution to grab a range?

  • Thread starter Thread starter Charles
  • Start date Start date
C

Charles

Hello

I am looking for a more elegant (and shorter) solution to grab the
content of an excel range into an array:

Dim grab
Dim value()
grab = Range("A1:A100").Value2
ReDim value(1 To UBound(grab))
For i = 1 To UBound(grab)
value(i) = grab(i, 1)
Next i

I could just use the array grab but then I have to carry a two
dimensional syntax all the way, although I know that grab only contains
one column. Or is there a simple instruction to "destroy " one of the
dimensions of an array?

Thanks ina advance
Charles
 
Dim myArr As Variant
myArr = Application.Transpose(Range("a1:a100").Value2)

IIRC, this will break in some versions of excel if you have more than 5461
elements.
 
I am impressed. Very efficient!

thanks
Charles


Dave said:
Dim myArr As Variant
myArr = Application.Transpose(Range("a1:a100").Value2)

IIRC, this will break in some versions of excel if you have more than 5461
elements.
Click to expand...
 
Hi

Public Sub Test()
Dim MyArray() As Variant
Dim rngTest As Range
Dim RowNum As Integer
Dim ColNum As Integer


Set rngTest = [TestRange]
RowNum = rngTest.Rows.Count
ColNum = rngTest.Columns.Count
ReDim MyArray(RowNum, ColNum)
MyArray = rngTest


End Sub

TestRange is a dynamic named range, defined in workbook, something like
=OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1;$1))


Arvi Laanemets
 
You can also readily "destroy" the second dimension of a 2-D
*horizontal* array. The most commonly referred to way is

myArr = Application.Transpose(Application.Transpose(Range("a1:z1").Value2))

Another is

myArr = Application.Index(Range("a1:z1").Value2, 1, 0)

Alan Beban
I am impressed. Very efficient!

thanks
Charles
Click to expand...
 
Back
Top