Hi Vic,

The array is defined as variants, with some elements being integer values,
other's are string literals.

So when i do the resize, the problem is the resize converts all integer
values to text ie. 8 becomes "8". And hence, i can't perform any
mathematical operations on cell contents until i convert the cell contents
back into integer values.

I tried using the PasteSpecial method to convert back to integer but doing
this really slows everything back down to a crawl again.


"Vic Eldridge" wrote:

Hi Bing,

The only way I could get this process to convert from values to text was to
dimension the array as String. Arrays dimmed as Longs,Doubles,Variants all
transferred to the worksheet as values. Is that where the problem lies ?


Regards,
Vic Eldridge


"Bing" wrote:

Hi Vic,

Thanks i tried it.. i got an almost 6x speed improvement. Only 1 thing..
this operation seems to automatically convert numbers as text. So rather
than have to call aRange.PasteSpecial to convert from text back to numbers,
is there a way to do this so that the resize operation treats the data as is,
without converting to text.

Thanks again.

"Vic Eldridge" wrote:

Hi Bing,

Firstly, try replacing the following code ,

for i = 0 to Ubound(newColumnArray)
aRange.Offset(0,i).Value = newColumnArray(i)
next i

with this...

aRange.Resize(, UBound(newColumnArray) + 1) = newColumnArray


What this does is transfer the whole array to the worksheet in one fell
swoop, as opposed to transferring each element one by one. The speed gain
comes from the fact that your calls to Excel's slow Range object are reduced
from multiple calls, to a single call.
Now take this concept a little further. Instead of creating a 1-dimensional
array of each and every row, then transferring each row separately, you
should create a 2-dimensional array of all rows and columns, and transfer the
whole
array in one hit after EOF has been reached.

You'll be removing hundreds of thousands of calls to the Range object.
You're in for a nice surprise. It's gonna fly !


Regards,
Vic Eldridge



"Bing" wrote:

Hi,

I written a macro to read in line by line multiple CSV files of varying
lengths.

Can anyone suggest a faster way in terms of runtime execution time to speed
this code up?

ie. I thought of reading in multiple lines in bulk to reduce I/O but VBA
doesn't seem to have a bulk read mode.

Basically i do the following:

Dim columnArrays as Variant
Dim newColumnArray as Variant
.
.
Do until EOF(inputFile)
Line Input #inputFile, inputLine
columnArray = Split(inputLine, ",")
....merge/massage/apply functions to certain columns in columnArray
and copy into newColumnArray
for i = 0 to Ubound(newColumnArray)
aRange.Offset(0,i).Value = newColumnArray(i)
next i
set aRnage = aRange.Offset(1,0)
Loop

Thanks to everyone for their valuable insights.