I've put together a simple macro for sifting through column A and transposing every 3 rows into one row... (i.e. A1,A2,A3 go to A1,B1,C1 , A4,A5,A6 go to A2,B2,C2 etc... )
it works really well, however, I'll be pushing the scope to the max, i.e trying to go to rows.count
I was wondering if anyone has an insight on on how to speed up the code, it takes about 40 seconds to get through 200,000 lines, and sort of bombs out (91 error) sometime after that ....
any ideas on some improvements?
here's the code :
Sub arrsampWORKS1() Dim array_example(3) Dim Destination As Range Dim p As Double 'StartTime = Timer For q = 0 To 40 p = q * 3 'Storing values in the array For i = 0 To 2 array_example(i) = Range("A" & i + 1 + p) Next Set Destination = Range(Cells(q + 1, 4), Cells(q + 1, 7)) Set Destination = Destination.Resize(1, 3) Destination.Value = array_example Next 'MsgBox Timer - StartTime & " seconds" End Sub
In general, if I have a macro that I want to be more efficient, I turn off screen updating and auto calculation at the beginning of the macro. By default, every time there is a change, excel will update every formula in the open workbooks.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
At the end of the macro, I re-enable them
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
This cuts the time dramatically, but is only useful if you do not need to recalculate everything.
If you need to calculate at a given time in the macro, you can use
Application.Calculate 'calculate everything
wksht.Calculate 'calculate a specified worksheet
If you still need better performance, then start reworking your code.
Writing to the spreadsheet and setting ranges take time to initialize and generally you can save time by storing more items in memory and then write at the end.
I would change your code to have 2 loops - first to read all of the data and create a temp variable that holds the info, then a 2nd loop that outputs all of the data.
Dim array_example(3) As Variant, alldata() As Variant
Dim Destination As Range, Data As Range
Dim p As Double, iCount As Double, iArraysCount As Double
Dim step As Integer
'StartTime = Timer
Set Data = Range("A1")
ReDim Preserve alldata(0)
iArraysCount = 0
step = 3
For iCount = 1 To 45 Step step
'Storing values in the array
For i = 0 To 2
array_example(i) = Data.Cells(iCount + i)
ReDim Preserve alldata(iArraysCount)
alldata(iArraysCount) = array_example
iArraysCount = iArraysCount + 1
Set Destination = Range("B1")
For iCount = 0 To UBound(alldata)
Destination.Cells(iCount + 1, 1).Value = alldata(iCount)(0)
Destination.Cells(iCount + 1, 2).Value = alldata(iCount)(1)
Destination.Cells(iCount + 1, 3).Value = alldata(iCount)(2)
'MsgBox Timer - StartTime & " seconds"