T
terryspencer2003
I currently transfer data from an excel array (100X17000) D1:CZ17520
to a named array C1:C17520 ("active array"). I use the Rnd function
with lower and upper bounds of 1 to 100 to randomly pick the columns
to chose from:
X = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
Range("activearray") = Range("activearray").Offset(0, X).Value
Is there a quicker to do this by pulling the data in D1:CZ17520 up
into a VBA array and transfering it from this VBA array back to excel?
Must I use a ForNExt loop for this? Or is there a quicker way doing
something like:
Load Data from excel into VB Array
Range("activearray") = VBArray
Secondly, my method above had me randomly picking the column variable
in the offset such that say a "7" pulls all the data from column J
into the activearray. But I now want to change the logic so that I
pull 24 row blocks from random columns instead of 17520 row blocks
from random columns. 17520/24=730 blocks. I am assuming that this
should be done using a VBA array. How would I do this?
Thanks
TS
to a named array C1:C17520 ("active array"). I use the Rnd function
with lower and upper bounds of 1 to 100 to randomly pick the columns
to chose from:
X = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
Range("activearray") = Range("activearray").Offset(0, X).Value
Is there a quicker to do this by pulling the data in D1:CZ17520 up
into a VBA array and transfering it from this VBA array back to excel?
Must I use a ForNExt loop for this? Or is there a quicker way doing
something like:
Load Data from excel into VB Array
Range("activearray") = VBArray
Secondly, my method above had me randomly picking the column variable
in the offset such that say a "7" pulls all the data from column J
into the activearray. But I now want to change the logic so that I
pull 24 row blocks from random columns instead of 17520 row blocks
from random columns. 17520/24=730 blocks. I am assuming that this
should be done using a VBA array. How would I do this?
Thanks
TS