VBA for inserting Rows with conditions

[leonpe34]

I have some data which is coming from a sensor, the time stamp must be every 10 minutes, however sometimes there happen data gaps which makes a problem for post data processing. Gaps can be filled in mainually but will take ages.
Example:
13-04-20 05:10:00
13-04-20 05:20:00
13-04-20 05:50:00
13-04-05 06:30:00
You see the gaps, data should be every 10 minutes. I would like to fix it by inserting rows and fill in the cell with the right time.

Maybe a macro that calculates the difference in between rows and when it is larger than 10 minutes then it insert row (s) and the time stamp that corresponds.

[paulked]

Hi and welcome to the forum.



Sub FillTimes()
Dim i As Long
For i = Cells(Rows.Count, 1).End(3).Row To 2 Step -1
Insrtd:
If Cells(i, 1) - Cells(i - 1, 1) > TimeValue("00:10:01") Then
Rows(i).Insert
Cells(i, 1) = Cells(i + 1, 1) - TimeValue("00:10:00")
GoTo Insrtd
End If
Next
End Sub

[p45cal]

…also this one. It works on the selected column of date/times (actually the leftmost column of any selected range):
Sub blah()
TenMins = TimeValue("00:10:00")
With Selection
For rw = .Rows.Count - 1 To 1 Step -1
Z = Round((.Cells(rw + 1, 1).Value - .Cells(rw, 1).Value) / TenMins, 0) - 1
If Z > 0 Then
.Cells(rw + 1, 1).EntireRow.Resize(Z).Insert
.Cells(rw, 1).Resize(Z + 1).DataSeries Step:=TenMins
End If
Next rw
End With
End Sub

[paulked]

Neat :thumb

[mdasifiqbal]

Sorry i did not spelt out the filtering criteria 1s the Filter will be Customer Name Column "J" and second filtering Criteria will be Column "A" which shows the location one customer might be having multiple location hence is Customer A has more then One location then the VBA created more than one excel file for each location of the same customer.