question on Array
 

I have a 2 dim array. I want to copy only certain part of the array into a
range cell.
I know that using Range(aRange).value = myarray will copy the entire myarray
to the worksheet. However, I only want to copy a certain set of continuous
row from myarray. For example I would like to copy myarray (5 to 10,*) into
the worksheet. Is it possible?
The only think I can think as a workaround is to copy myarray(5 to 10) into
a brand new array and it does take time to loop through myarray by row and
then by column into the new array.

Hope that makes sense.
Thanks

question on Array
 

Hi Matelot,

The only think I can think as a workaround is to copy myarray(5 to 10)
into
a brand new array and it does take time to loop through myarray by row and

I think that this would be the standard approach and
looping through an array is normally very fast. Try, for
example something like:

'=============
Public Sub Tester()
Dim ArrIn As Variant
Dim ArrOut(1 To 501, 1 To 20)
Dim i As Long, j As Long, k As Long

ArrIn = Range("A1:T1000").Value
For i = 500 To 1000
k = k + 1
For j = LBound(ArrIn, 2) To UBound(ArrIn, 2)
ArrOut(k, j) = ArrIn(i, j)
Next j
Next i
ActiveSheet.Next.Range("A1").Resize(UBound(ArrOut, 1), _
UBound(ArrOut, 2)).Value = ArrOut
End Sub
'<<=============



---
Regards,
Norman


"matelot" wrote in message
...
I have a 2 dim array. I want to copy only certain part of the array into a
range cell.
I know that using Range(aRange).value = myarray will copy the entire
myarray
to the worksheet. However, I only want to copy a certain set of continuous
row from myarray. For example I would like to copy myarray (5 to 10,*)
into
the worksheet. Is it possible?
The only think I can think as a workaround is to copy myarray(5 to 10)
into
a brand new array and it does take time to loop through myarray by row and
then by column into the new array.

Hope that makes sense.
Thanks

question on Array
 

Norman,
Thanks for the reply. I was hoping that there may be a faster more optimized
way. Your way would work as well.

Thanks

"Norman Jones" wrote:

Hi Matelot,

The only think I can think as a workaround is to copy myarray(5 to 10)
into
a brand new array and it does take time to loop through myarray by row and

I think that this would be the standard approach and
looping through an array is normally very fast. Try, for
example something like:

'=============
Public Sub Tester()
Dim ArrIn As Variant
Dim ArrOut(1 To 501, 1 To 20)
Dim i As Long, j As Long, k As Long

ArrIn = Range("A1:T1000").Value
For i = 500 To 1000
k = k + 1
For j = LBound(ArrIn, 2) To UBound(ArrIn, 2)
ArrOut(k, j) = ArrIn(i, j)
Next j
Next i
ActiveSheet.Next.Range("A1").Resize(UBound(ArrOut, 1), _
UBound(ArrOut, 2)).Value = ArrOut
End Sub
'<<=============



---
Regards,
Norman


"matelot" wrote in message
...
I have a 2 dim array. I want to copy only certain part of the array into a
range cell.
I know that using Range(aRange).value = myarray will copy the entire
myarray
to the worksheet. However, I only want to copy a certain set of continuous
row from myarray. For example I would like to copy myarray (5 to 10,*)
into
the worksheet. Is it possible?
The only think I can think as a workaround is to copy myarray(5 to 10)
into
a brand new array and it does take time to loop through myarray by row and
then by column into the new array.

Hope that makes sense.
Thanks

question on Array
 

Hi Matelot,

'----------------
Thanks for the reply. I was hoping that there may be a faster
more optimized way. Your way would work as well.
'----------------

Try timing the operation and you will see that it can
be extremely fast.


---
Regards,
Norman