Anyone know how to copy a Range of Cells into an Array?

GeorgeAdamson · Feb 25th, 2002, 08:59 AM

Is there a command to do it or do I have to loop through the Range adding each cell value to the array one by one?

Thanks

dk · Feb 25th, 2002, 09:05 AM

It's fairly straightforward to assign the cells in a range to an array. It's also a very quick way of working with large amounts of worksheet data.

Sub GetArray()
Dim vArray()
vArray = Range("A1:E10")
'vArray now contain a 10 x 5 array of values
'You can work with it and then assign it back to a range - this is much quicker
'than populating cells one at a time through a loop e.g.

Range("F1:J10").Value = vArray

End Sub

HTH,
D

GeorgeAdamson · Feb 25th, 2002, 09:18 AM

I knew there had to be simple way. and this really is simple! Nice one, thanks DK

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 25th, 2002, 08:59 AM	#1
GeorgeAdamson New Member Join Date: Feb 2002 Posts: 14	Is there a command to do it or do I have to loop through the Range adding each cell value to the array one by one? Thanks

Feb 25th, 2002, 09:05 AM	#2
dk MrExcel MVP Join Date: Feb 2002 Location: Sydney, Australia Posts: 2,908	It's fairly straightforward to assign the cells in a range to an array. It's also a very quick way of working with large amounts of worksheet data. Sub GetArray() Dim vArray() vArray = Range("A1:E10") 'vArray now contain a 10 x 5 array of values 'You can work with it and then assign it back to a range - this is much quicker 'than populating cells one at a time through a loop e.g. Range("F1:J10").Value = vArray End Sub HTH, D

Feb 25th, 2002, 09:18 AM	#3
GeorgeAdamson New Member Join Date: Feb 2002 Posts: 14	I knew there had to be simple way. and this really is simple! Nice one, thanks DK