sequence Macro help

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

Originally Posted by 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.

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.

Originally Posted by Rick Rothstein

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.

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.

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

Please Login or Register  to view this content.

Originally Posted by Rick Rothstein

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

Please Login or Register  to view this content.

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?

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...

Please Login or Register  to view this content.

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.

Originally Posted by 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...

Please Login or Register  to view this content.

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?

Originally Posted by googs91

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?

Originally Posted by Rick Rothstein

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

alansidman

Sorry guess im pretty new to forums.

Originally Posted by googs91

its still missing about 1hr and 1 min of data

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

Please Login or Register  to view this content.

Works PERFECT thank you so much for your help. I will add this in my other post.

Sorry about the miscommunication earlier.

Hello I am wondering how the code would be modified to keep data in corresponding columns? For example, the first few rows of my data look like this:
Time Pulse VVTI
8/5/2019 9:09 109 11.24
8/5/2019 9:14 85 11.28
8/5/2019 9:24 80 11.16
8/5/2019 9:30 88 11.17
8/5/2019 9:33 112 10.77
8/5/2019 9:35 72 11.65
8/5/2019 9:39 77 11.43
8/5/2019 9:47 78 11.26
8/5/2019 9:49 85 11.11
8/5/2019 9:52 86 11.18
8/5/2019 9:54 88 10.96
I need it like this for 1440 rows:
8/5/2019 9:09 109 11.24
8/5/2019 9:10
8/5/2019 9:11
8/5/2019 9:12
8/5/2019 9:13
8/5/2019 9:14 85 11.28
Thank you, HeidiO

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