Quote Originally Posted by mitko007
I read that VBA perfroms much better when we use Variant arrays
Yes, by reducing the number of write operations to the sheet can save a lot of time; instead of writing to one cell at a time, a block of cells can be written to in one line, and it's quite quick.
In the following, I've reduced the number of write operations to 2 per time block: one for the 2 columns of split times, and one for the index.
If this is still too slow, come back, because it can be tweaked so that only one write operation is needed for the whole shooting match.[VBA]Sub AddSecsMulti_pd01()
Range("G2:I" & Application.Max(2, Range("G" & Rows.Count).End(xlUp).Row)).Clear 'Contents
Set DestTop = Range("G" & Rows.Count).End(xlUp).Offset(1)
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
StartTime = Range("A" & i).Value
EndTime = Range("B" & i).Value
ReDim RArray(1 To Round((EndTime - StartTime) * 86400, 0), 1 To 2)
J = 1
For s = StartTime To EndTime Step 1 / 86400
RArray(J, 1) = s
RArray(J, 2) = s + 1 / 86400
J = J + 1
Next s
DestTop.Resize(J - 1, 2).Value = RArray 'split times
DestTop.Offset(, 2).Resize(J - 1).Value = Range("D" & i).Value 'index
Set DestTop = DestTop.Offset(J - 1)
Next i
Range("G2:H" & Range("G" & Rows.Count).End(xlUp).Row).NumberFormat = "dd.mm.yyyy hh:mm:ss"
End Sub
[/VBA]