sequence Macro help

[googs91]

I am trying to figure out how to build a macro to spot missing sequence and add in a new row when found.

There should be 1440 data points in column A
The date and time formatting goes as followed

10/10/2019 7:41
10/10/2019 7:42
10/10/2019 7:43
10/10/2019 7:44
10/10/2019 7:45
10/10/2019 7:46
10/10/2019 9:52
10/10/2019 9:53
10/10/2019 9:54
10/10/2019 9:55
10/10/2019 9:56

I would like the macro to find that its missing the data between 7:47 and 9:51 and add in blank rows. The time is also in military time so the format is
10/10/2019 00:00 thru 10/10/2019 23:59

[JLGWhiz]

If the first two values are OK, then you can simply select them, put the mouse pointer on the node at bottom right of the selected area and drag down as far as needed. It will automatically fill in any missing increments.

[Rick Rothstein]

If the first two values are OK, then you can simply select them, put the mouse pointer on the node at bottom right of the selected area and drag down as far as needed.

I am just guessing here, but I think the problem with that approach may be that it does not keep data in other columns aligned with their respective time slots.

[googs91]

I am just guessing here, but I think the problem with that approach may be that it does not keep data in other columns aligned with their respective time slots.

When attempting this it just adds more time slots based on the last number. does not open up blank rows.

[googs91]

what I have found in the formatting of the date and time is that every entry, which is one min apart, is .0006944444 off of the next.

I have tried using Kutools for this issue but it does not seem to work well for this.

[alansidman]

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this.

I have added the crosspost reference for you today. Please comply with this and all our rules in the future

https://www.mrexcel.com/forum/excel-...blank-row.html

[Rick Rothstein]

Assuming your data starts in cell A1, give this macro a try...

Sub FillInMissingTimeSlots()
  Dim R As Long
  Application.ScreenUpdating = False
  For R = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    If Minute(Cells(R, "A").Value) - Minute(Cells(R - 1, "A")) > Minute(TimeSerial(0, 1, 0)) Then
      Cells(R, "A").Resize(Minute(Cells(R, "A").Value) - Minute(Cells(R - 1, "A").Value) - 1).EntireRow.Insert
    End If
  Next
  On Error GoTo NoBlanks
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C+TIME(0,1,0)"
    .Value = .Value
  End With
NoBlanks:
  Application.ScreenUpdating = True
End Sub

[googs91]

Assuming your data starts in cell A1, give this macro a try...

Sub FillInMissingTimeSlots()
  Dim R As Long
  Application.ScreenUpdating = False
  For R = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    If Minute(Cells(R, "A").Value) - Minute(Cells(R - 1, "A")) > Minute(TimeSerial(0, 1, 0)) Then
      Cells(R, "A").Resize(Minute(Cells(R, "A").Value) - Minute(Cells(R - 1, "A").Value) - 1).EntireRow.Insert
    End If
  Next
  On Error GoTo NoBlanks
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C+TIME(0,1,0)"
    .Value = .Value
  End With
NoBlanks:
  Application.ScreenUpdating = True
End Sub

You Sir are a life saver. worked like a charm. I have spent hours and hours trying to get that to work.
Only question I have is my rows start at A4. Where do I adjust that in the macro?

[Rick Rothstein]

First off... did you read Message #6 yet? You will need to adhere to that in the future. Also, be nice and post the link back to this thread in your MrExcel thread there so that the volunteers there do not waste their time trying to solve your now solved problem.

Here is the revised code for data starting in cell A4...

Sub FillInMissingTimeSlots()
  Dim R As Long
  Application.ScreenUpdating = False
  For R = Cells(Rows.Count, "A").End(xlUp).Row To 5 Step -1
    If Minute(Cells(R, "A").Value) - Minute(Cells(R - 1, "A")) > Minute(TimeSerial(0, 1, 0)) Then
      Cells(R, "A").Resize(Minute(Cells(R, "A").Value) - Minute(Cells(R - 1, "A").Value) - 1).EntireRow.Insert
    End If
  Next
  On Error GoTo NoBlanks
  With Range("A4", Cells(Rows.Count, "A").End(xlUp))
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C+TIME(0,1,0)"
    .Value = .Value
  End With
NoBlanks:
  Application.ScreenUpdating = True
End Sub

In the future, you should give us the relevant information like this so we don't have to go back and modify our solutions for you.

[googs91]

First off... did you read Message #6 yet? You will need to adhere to that in the future. Also, be nice and post the link back to this thread in your MrExcel thread there so that the volunteers there do not waste their time trying to solve your now solved problem.

Here is the revised code for data starting in cell A4...

Sub FillInMissingTimeSlots()
  Dim R As Long
  Application.ScreenUpdating = False
  For R = Cells(Rows.Count, "A").End(xlUp).Row To 5 Step -1
    If Minute(Cells(R, "A").Value) - Minute(Cells(R - 1, "A")) > Minute(TimeSerial(0, 1, 0)) Then
      Cells(R, "A").Resize(Minute(Cells(R, "A").Value) - Minute(Cells(R - 1, "A").Value) - 1).EntireRow.Insert
    End If
  Next
  On Error GoTo NoBlanks
  With Range("A4", Cells(Rows.Count, "A").End(xlUp))
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C+TIME(0,1,0)"
    .Value = .Value
  End With
NoBlanks:
  Application.ScreenUpdating = True
End Sub

In the future, you should give us the relevant information like this so we don't have to go back and modify our solutions for you.

Understood. My apologies.

So with the macro the first time it worked great now after changing to A4 this is all it does
10/10/2019 7:43
10/10/2019 7:44
10/10/2019 7:45
10/10/2019 7:46
10/10/2019 7:47 Added
10/10/2019 7:48 Added
10/10/2019 7:49 Added
10/10/2019 7:50 Added
10/10/2019 7:51 Added
10/10/2019 9:52
10/10/2019 9:53
10/10/2019 9:54

Any thoughts?

[Rick Rothstein]

So with the macro the first time it worked great now after changing to A4 this is all it does
10/10/2019 7:43
10/10/2019 7:44
10/10/2019 7:45
10/10/2019 7:46
10/10/2019 7:47 Added
10/10/2019 7:48 Added
10/10/2019 7:49 Added
10/10/2019 7:50 Added
10/10/2019 7:51 Added
10/10/2019 9:52
10/10/2019 9:53
10/10/2019 9:54

Isn't that what it is supposed to do? Your missing times were the ones you marked "Added" next to... what else was the code supposed to do?

[googs91]

Isn't that what it is supposed to do? Your missing times were the ones you marked "Added" next to... what else was the code supposed to do?

its still missing about 1hr and 1 min of data

[Rick Rothstein]

its still missing about 1hr and 1 min of data

Argh! I completely missed that!!! Give this code a try...

Sub FillInMissingTimeSlots()
  Dim R As Long, T1 As Date, T2 As Date
  Application.ScreenUpdating = False
    For R = Cells(Rows.Count, "A").End(xlUp).Row To 5 Step -1
      T1 = 60 * Hour(Cells(R - 1, "A").Value) + Minute(Cells(R - 1, "A").Value)
      T2 = 60 * Hour(Cells(R, "A").Value) + Minute(Cells(R, "A").Value)
      If T2 - T1 > Minute(TimeSerial(0, 1, 0)) Then
      Cells(R, "A").Resize(T2 - T1 - 1).EntireRow.Insert
    End If
  Next
  On Error GoTo NoBlanks
  With Range("A4", Cells(Rows.Count, "A").End(xlUp))
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C+TIME(0,1,0)"
    .Value = .Value
  End With
NoBlanks:
  Application.ScreenUpdating = True
End Sub

[alansidman]

@googs91

When you reply with Quote you are just adding clutter to the thread unless it is necessary to distinguish between posts provided by others. Simply click on reply.

[googs91]

alansidman

Sorry guess im pretty new to forums.

[HeidiO]

Disregard my message please - I had copied the wrong code - the correct code DOES work like a charm. Thank you.