Transfer Worksheet into Array in one chunk with VBA?

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

I've searched the Microsoft.public.Excel.misc archives and my VBA books but have
not found the answer to this query

If you have a spreadsheet with numbers that has 40 columns and 3000 rows how do
you transfer that sheet into a 2D array in one chunk without using a loop?

For instance you can transfer an 3000x40 array into a spreadsheet by

Activeworkbook.Sheets(1).Cells(1,1).Resize(3000,40) = Array

How do you go the other way?

I've tried

Dim Array () as Variant
ReDim Array (1 to 3000, 1 to 40)
Array() = Activeworkbook.Sheets(1).Range(Columns(1),Columns(40).Value

but this did not work.

Thanks for any help.

Dennis
 
If you have a spreadsheet with numbers that has 40 columns and 3000
rows how do you transfer that sheet into a 2D array in one chunk
without using a loop?
Click to expand...
....

*Don't* use a dimensioned array, use a variant.

Dim a As Variant
a = SomeRangeRef.Value

There may be size restrictions. I doubt this would work for 40-by-3000
(120,000) cell ranges.
 
Thanks Brian but didn't work.

The below code gives me the error "Run Time Error 438: Object doesn't support
this property or method" at the
myArr = .Sheets(1).Range(.Columns(1), .Columns(3)).Value

What did I do wrong?

Thanks

Dennis


Option Explicit

Sub TestArrayInOut()
'This sub uses an array to populate the spreadsheet
Dim AccessTime As Date
Dim XLWB As Workbook
Dim LastRow As Long, LastCol As Long, NumOfRows As Integer
AccessTime = Now()
Set XLWB = ActiveWorkbook
NumOfRows = 10
With XLWB
'Add Data to the work book
Dim icol As Long, irow As Long, DataArr(1 To 10, 1 To 3)
For irow = 1 To NumOfRows
For icol = 1 To 3
DataArr(irow, icol) = irow / icol
Next icol
Next irow
.Sheets(1).Cells(1, 1).Resize(10, 3).Value = DataArr
LastRow = .Worksheets(1).Cells(Rows.Count, "a").End(xlUp).Row
LastCol = .Worksheets(1).Range("A1").End(xlToRight).Column

Dim myArr As Variant
'put spreadsheet back into an array
myArr = .Sheets(1).Range(.Columns(1), .Columns(3)).Value
End With

MsgBox ("LastCol=" & LastCol & " LastRow=" & LastRow & _
" XTime=" & Format(Now() - AccessTime, "hh:mm:ss.s"))
End Sub
 
...
...
The below code gives me the error "Run Time Error 438: Object doesn't support
this property or method" at the
myArr = .Sheets(1).Range(.Columns(1), .Columns(3)).Value

What did I do wrong?
Click to expand...

What do you think .Sheets(1).Range(.Columns(1), .Columns(3)) is? FWIW, the
statement

MsgBox TypeName(.Sheets(1).Range(.Columns(1), .Columns(3)))

throws the same runtime error. The expression

Sheets(1).Range(.Columns(1), .Columns(3))

isn't a valid range object. I'm going to guess you mean

Sheets(1).Range("A1").Resize(LastRow, LastCol)

If so, change your array assignment statement to

myArr = .Sheets(1).Range("A1").Resize(LastRow, LastCol).Value
 
Thanks Harlan.

I thought it was a valid range object but was obviously wrong!

Thanks for the code below. I didn't know that you could define the whole sheet
range as .Range("A1").Resize(LastRow, LastCol)..neat I can really use this!

Thanks again

Dennis
 
Just a note:

You have this:

With XLWB
'<snipped>
Dim myArr As Variant
'put spreadsheet back into an array
myArr = .Sheets(1).Range(.Columns(1), .Columns(3)).Value
End With

The dots in front of .columns(1) and .columns(3) mean that those objects refer
to the previous With statement. But your previous with statement is "With XLWB"
and XLWB is a workbook. And workbooks don't have columns--worksheets do.

maybe:
with XLWB
'still snipped
with .sheets(1)
myarr = .range(.columns(1),.columns(3)).value
end with
end with

But this will make your array 65536 rows by 3 columns. Probably not what you
wanted.
 
...

*Don't* use a dimensioned array, use a variant.

Dim a As Variant
a = SomeRangeRef.Value
Click to expand...
I've believed that to be true even though it has always bugged me. So,
I tried the following with XL2002:

Option Explicit

Sub testIt()
Dim Arr()
With ActiveSheet.Range("a1").CurrentRegion
ReDim Arr(.Rows.Count, .Columns.Count)
Arr = .Value
End With
End Sub

Worked just fine. Maybe, it was a change implemented with XL2002.
There may be size restrictions. I doubt this would work for 40-by-3000
(120,000) cell ranges.
Click to expand...
Again, worked fine with XL2002 with a 40,000 x 3 cells range. I
imagine it was yet another (undocumented?) limit that was removed in
XL2002 together with the limit on the Transpose function. With the
same 40,000 x 3 cells range, the following also worked:

Option Explicit

Sub testIt()
Dim Arr()
With ActiveSheet.Range("a1").CurrentRegion
ReDim Arr(.Columns.Count, .Rows.Count)
Arr = Application.WorksheetFunction.Transpose(.Value)
End With
End Sub

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
The first testIt below works in xl2000, SR-1; no size limit. I suspect
it worked in Excel5 and 97.

The second testIt below is subject to the 5461-cell limit in xl2000 SR-1

Alan Beban

Tushar said:
...

*Don't* use a dimensioned array, use a variant.

Dim a As Variant
a = SomeRangeRef.Value
Click to expand...

I've believed that to be true even though it has always bugged me. So,
I tried the following with XL2002:

Option Explicit

Sub testIt()
Dim Arr()
With ActiveSheet.Range("a1").CurrentRegion
ReDim Arr(.Rows.Count, .Columns.Count)
Arr = .Value
End With
End Sub

Worked just fine. Maybe, it was a change implemented with XL2002.

There may be size restrictions. I doubt this would work for 40-by-3000
(120,000) cell ranges.
Click to expand...

Again, worked fine with XL2002 with a 40,000 x 3 cells range. I
imagine it was yet another (undocumented?) limit that was removed in
XL2002 together with the limit on the Transpose function. With the
same 40,000 x 3 cells range, the following also worked:

Option Explicit

Sub testIt()
Dim Arr()
With ActiveSheet.Range("a1").CurrentRegion
ReDim Arr(.Columns.Count, .Rows.Count)
Arr = Application.WorksheetFunction.Transpose(.Value)
End With
End Sub
[/QUOTE]
 
The first testIt below works in xl2000, SR-1; no size limit. I suspect
it worked in Excel5 and 97.
Click to expand...
...

It fails as a syntax error in Excel 97, so your instincts appear flawed. Very
likely is won't work in Excel 5/95 either.
 
Back
Top