Using loop(s) to delete rows based on criteria

Hello all - new user who surfed in looking for help with VBA on creating a somewhat complicated (my opinion, might be easy for someone here!) Macro. It's been about 10 years since I last wrote a Macro. Things have changed and my memory isn't helping me much, either. I dabbled a little on some basic things and more or less got them working, but this particular task needs much more than my VBA/Macro skill set entails. I would certainly appreciate help from anyone willing to take on the challenge!

I will attempt to explain my problem with as much detail as possible, but I realize I may omit necessary information, confuse the terminology, or otherwise make no sense at all. In such cases, I will answer questions and provide additional details to the best of my ability. I have attached a workbook with two small data sets (there are many other columns in the original dataset, I reduced this down for the purpose of this exercise. Additionally, most of my datasets are thousands to tens of thousands of lines long), each in a separate worksheet, as well as a desired outcome in a third worksheet.

Here's the jist: I have two large datasets, each with a 24 hour (00:00:00) timestamp associated with every line of data. I need to get each line (row) of data between the two worksheets to be within 1 second (00:00:01) of each other. Perhaps it's not even possible to do this in VBA - the little I dabbled I kept getting hung up on the within 1 second issue. However, if it is possible, I think a loop or series loops is what I need to accomplish this task. The devices used to record the data do so at different iterations: one is about every other second, the other is about every second. However, sometimes they jump a second ahead (mostly the second dataset).

My ideas with the loop/loops is this:
Step 1 - I first want to check to see if the values for each worksheet (values are based on Worksheet 1) on Row 1 are identical. If they are, then move on to Row 2.
Step 2 - If they are not identical, then I want to compare the value in Row 1 of Worksheet 1 to the value in Row 2 of Worksheet 2. If the value of Row 2/Worksheet 2 is ≤00:00:01 to that of Row 1 Worksheet 1, then I want to delete Row 1 of Worksheet 2.
Step 3 - If Row 2/Worksheet 2 is not ≤00:00:01 to that of Row 1 Worksheet 1 (no or bad data in the cell, for whatever reason - I'll most likely have it weeded out before running the Macro. I know this step might seem unnecessary, but I'd rather have this sort of 'check' in here, so I don't delete a bunch of data inadvertently, because there was a gap somewhere), then move to the next Row in Worksheet 1, and begin with Step 1.


Quick Example:

Row Worksheet 1 Worksheet 2
1 0:58:00 0:58:00
2 0:58:02 0:58:01
3 0:58:04 0:58:02
4 0:58:06 0:58:03
5 0:58:08 0:58:04
6 0:58:10 0:58:05
7 0:58:12 0:58:07
8 0:58:14 0:58:08
9 0:58:16 0:58:09
10 0:58:18 0:58:10



Quick Desired Result

Row Worksheet 1 Worksheet 2
1 0:58:00 0:58:00
2 0:58:02 0:58:02
3 0:58:04 0:58:04
4 0:58:06 0:58:07
5 0:58:08 0:58:08
6 0:58:10 0:58:10


I can always line up the two datasets initially to the exact same time (row 1), and those Excel files will not have any headers. I'm using Excel 2010. I've read that loops work best from bottom to top, but I don't really think that would work for my scenario.

Thanks to any brave souls willing to take on this task, or offer advice. I will be very grateful to get a working Macro, as I have lots and lots of these to match up, and deleting line-by-line is incredibly mind-numbing and time consuming. I'll keep in touch here, to answer questions as they arise. I will be out of the office tomorrow through Sunday, but will try to find time to check in here over the holiday weekend.


Fish_Bio

It is very rare on this forum to observe a person who can form a coherent sentence; to that, I applaud you.

At this point I have one question with regards to the below statement:

If the value of Row 2/Worksheet 2 is ≤00:00:01 to that of Row 1 Worksheet 1, then I want to delete Row 1 of Worksheet 2.

If we are to compare Row 2 Worksheet 2 with Row 1 Worksheet 1, why would we delete Row 1 Worksheet 2 when it is not involved in the comparison. If I were to follow your described logic,Row 2 Worksheet 2, which has a value of 0:58:01, would not be deleted.

stnkynts,

Thanks for taking a look at my puzzle here. Let me try to explain it a bit better.

The portion of 'step 2' you've quoted is done under the assumption that the values in Row 1 of each Worksheet are not equal. I've modified my 'quick example' in an effort to provide more clarity. 00:58:02 and 00:58:01 are not equal, so the loop will look to the next Row (Row 3 in the modified example below) in Worksheet 2 for an acceptable value. It would find one, and then delete Row 1/Worksheet 2 'moving up' that dataset by a row, and the values in Row 1 would be within the one second constraint. In the original example, Row 1 is identical between both worksheets, so the loop should just move to Row 2, where it would find the values to not be identical, and it would look to Row 3/Worksheet 2 for an acceptable value.

Before
2 0:58:02 0:58:01
3 0:58:04 0:58:02
4 0:58:06 0:58:03
5 0:58:08 0:58:04
6 0:58:10 0:58:05
7 0:58:12 0:58:07
8 0:58:14 0:58:08
9 0:58:16 0:58:09
10 0:58:18 0:58:10

After
2 0:58:02 0:58:02
3 0:58:04 0:58:03
4 0:58:06 0:58:04
5 0:58:08 0:58:05
6 0:58:10 0:58:07
7 0:58:12 0:58:08
8 0:58:14 0:58:09
9 0:58:16 0:58:10
10 0:58:18 0:58:11

Was that helpful, or more confusing?

Fish_Bio

xladept,

Thank you! Good catch on my spelling. It's actually kind of fun to say it with an extra e in there!

It appears everything works until the very end, on the End If line. The prompt is:

Compile error:
Expected End Sub

I'm not sure if this is something I can debug with a little time spent thinking about it on my own. I will try if I have some time over the next few days, otherwise next week. One other thing - will this code just delete the single cell, or an entire row? For brevity reasons, I did not include the 6-15 other columns that are normally present with the timestamp. I'd like the entire row to be deleted when the timestamps don't match.

Thanks!

Fish_Bio