Hi everybody,
i need some help on the following problem:
I have a macro which reads a range from Start Date/Time andd End Date/time a then splits this range into 1 second increments (see screenshot)
here is the code i use for doing this:
[vba]Sub AddSecsMulti()
Range("G2:G" & Range("G" & Rows.Count).End(xlUp).Row).Clear
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
Range("G" & Rows.Count).End(xlUp).Offset(1) = Range("A" & i)
Do
With Range("G" & Rows.Count).End(xlUp)
.Offset(1) = DateAdd("s", 1, .Value)
.Offset(0, 1) = DateAdd("s", 1, .Value)
.Offset(0, 2) = Range("D" & i)
If .Value = Range("B" & i) Then .Offset(0, 1).Clear: .Offset(1).Clear: Exit Do
End With
Loop
Next i
Range("G2:G" & Range("G" & Rows.Count).End(xlUp).Row).NumberFormat = "dd.mm.yyyy hh:mm:ss"
Range("H2:H" & Range("H" & Rows.Count).End(xlUp).Row).NumberFormat = "dd.mm.yyyy hh:mm:ss"
End Sub
[/vba]
It works fine for smaller data sets, but i have to deal with large data sets and it goes to slow. Is there a way that the code can be fixed so that it works quicker. I read that VBA perfroms much better when we use Variant arrays but i'm not familiar with them
Any help will be appreciated.
Thanks