Excel VB How To Question

[Bruce007]

Does any one know how to read in an entire sheet into an array quickly?

 

[SQLBI]

Bruce,

A worksheet contains 256*65536 cells, why would you want to store this amount of data in an array. Excel already does this to some extent in the sheet itself so by putting it into a new array, you're storing the same data twice in memory and this will obviously make everything very slow.

Perhaps you could post a little more info regarding what you're trying to achieve.




Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[TonyJollans]

Hi Bruce007,

Guess it depends what you mean by quickly

A single statement - myArray = Cells should do it but I run out of memory if I try it. More practically, perhaps, myArray = ActiveSheet.UsedRange.Cells will probably do what you want, but a worksheet is already an array so why do you need a copy of it in your code?

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[SkipVought]

Bruce,

And the reason is....

Dim Myarray()
Sub Atest()
    Dim MyArray()
    ReDim MyArray(2, ActiveSheet.UsedRange.Count)
    i = LBound(MyArray, 2)
    For Each c In ActiveSheet.UsedRange
        MyArray(0, i) = c.Value
        MyArray(1, i) = c.Row
        MyArray(2, i) = c.Column
        i = i + 1
    Next
End Sub

???

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Bruce007]

Thanks for the information. I guess that I should have been a little more detailed in what I am doing. I am bring data in from other workbooks into mine. From here I will be working the data. I have been accessing the combined data as Worksheets(name).cells(row,col).value. Is there a better way? Mention was made that the worksheet is already an array in memory so does that mean that Worksheets(name).cells(row,col).value is a memory resident array?

 

[SkipVought]

Absolutely!

faq707-4105 How Can I Make My Code Run Faster?



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884