How do I copy a data from a single column into an array and back into another column?
 

Hi,

I'm posting this actually as an answer to the question I kept googling
and googling for but couldn't find a straightforward answer for. Maybe
others will reply with a better way to solve this problem.

Anyways, I have data in a single column in one Excel spreadsheet that
I want to extract, process in memory, and insert into another
spreadsheet.

here's the code I used

Dim idArray() As Variant
ReDim idArray(13 to srcRange.Rows.Count,1) 'note that this is a 2d
array even though I only need a single column.

For i = 13 to srcRange.Rows.Count
idArray(i,0) = srcRange.Cells(i,1).Value
Next

'Process array data
.....
'Put data back starting at second cell of first column
Worksheets(1).Range("A2").Resize(UBound(idArray)). Value = idArray

The part that kept vexing me was that idArray needs to be 2
dimensional even though I only need to copy a single column into the
array and back. Hopefully this will keep someone else from going batty
trying to figure this out!

Thanks,
Walter

How do I copy a data from a single column into an array and back into another column?
 

some people just use

Worksheets(1).Range("A2").Resize( _
UBound(idArray),1).Value = Application.Transpose(idArray)

to avoid having to have a 2D array.

--
Regards,
Tom Ogilvy

wrote in message
ups.com...
Hi,

I'm posting this actually as an answer to the question I kept googling
and googling for but couldn't find a straightforward answer for. Maybe
others will reply with a better way to solve this problem.

Anyways, I have data in a single column in one Excel spreadsheet that
I want to extract, process in memory, and insert into another
spreadsheet.

here's the code I used

Dim idArray() As Variant
ReDim idArray(13 to srcRange.Rows.Count,1) 'note that this is a 2d
array even though I only need a single column.

For i = 13 to srcRange.Rows.Count
idArray(i,0) = srcRange.Cells(i,1).Value
Next

'Process array data
....
'Put data back starting at second cell of first column
Worksheets(1).Range("A2").Resize(UBound(idArray)). Value = idArray

The part that kept vexing me was that idArray needs to be 2
dimensional even though I only need to copy a single column into the
array and back. Hopefully this will keep someone else from going batty
trying to figure this out!

Thanks,
Walter