If you set the dimensions of the array in the Dim statement, you cannot
change them after.
However, if Dim a dynamic array, you can. e.g.
Dim IntArray() as Integer
Redim IntArray(1 To 1000, 1 to 9)
'do something with IntArray
Redim IntArray(1 To 3)

NickHK

"JackRnl" wrote in
message ...

hi Ken,

Thanks, the code does work but I still have a question about it.

I HAVE to use
Dim vaValues as Variant
instead of
Dim vaValues as Integer
or
Dim vaValues(10000,9) as integer
I see

But I can ReDim it later I found out and the code I made
*****
Sub Test()
Const NumRows = 10000
Const NumColumns = 9
Const NumSets = 10
Dim arrValues As Variant
Dim rngSet As Range
Dim NumSet, i, j, ColOffset As Integer

With Worksheets("Test")
ColOffset = 0
For NumSet = 1 To NumSets
Set rngSet = Range(.Cells(1, ColOffset + 1),
Cells(NumRows, ColOffset + NumColumns))
arrValues = rngSet.Value
ReDim arrValues(NumRows, NumColumns) As Integer
For i = 1 To NumRows
For j = 1 To NumColumns
arrValues(i, j) = arrValues(i, j) * 2 'something
Next j
Next i
rngSet.Value = arrValues
rngSet.Columns.AutoFit
ColOffset = ColOffset + NumColumns + 1
Next NumSet
End With
End Sub
*****
does exactly what I want.

My questions is, as I know variables (and arrays) of type Variant use
much more space compared to variables of other types (like Integer),
does it mean my memory consumption will be much higher BEFORE and even
AFTER the ReDim?
This could be a problem for me as the code (also) has to run on PCs
with just a little memory. Of course I know I have to test it anyway
and maybe use a loop to handle all 10000 rows in chunks of let's say
1000 at a time.


--
JackRnl
------------------------------------------------------------------------
JackRnl's Profile:
http://www.excelforum.com/member.php...o&userid=37172
View this thread: http://www.excelforum.com/showthread...hreadid=568889