VBA macro to delete rows with first and third repeating variable

[LambChoptheKid]

Hello, I am trying to create a macro that will handle data that often includes 3x repeated timestamps. For those repeated timestamps, I need to keep the second iteration and discard the rows with the first and the third.

Example:

These columns will continue in this manner with three distinct values for each timestamp. Sometimes there is only one value for the timestamp, these values should be left alone

Is there a way to write a macro that will delete the first and third repeat?

Example of what I want the sheet to look like after running the macro:

Idea is to keep the second value and delete the rows with the first and third. I've written some VBA but I'm really scratching my head trying to make something work here. Also I have no way to use XL2BB so outside of a screenshot I can't give much more than what I've already provided.

If I have been unclear in my post I'd be happy to clarify.

If anyone is able to provide some insight as to how to tackle this, any guidance will be greatly appreciated!

 

[Georgiboy]

Maybe a formula option:

Book1
	A	B	C	D	E
1	Date Time			Date Time
2	07/01/2023 01:15	a		07/01/2023 01:15	b
3	07/01/2023 01:15	b		07/01/2023 01:30	e
4	07/01/2023 01:15	c		07/01/2023 01:45	h
5	07/01/2023 01:30	d		07/01/2023 02:00	j
6	07/01/2023 01:30	e		07/01/2023 02:15	l
7	07/01/2023 01:30	f
8	07/01/2023 01:45	g
9	07/01/2023 01:45	h
10	07/01/2023 01:45	i
11	07/01/2023 02:00	j
12	07/01/2023 02:15	k
13	07/01/2023 02:15	l
14	07/01/2023 02:15	m
Sheet1

Cell Formulas
Range	Formula
D2:E6	D2	=LET( rng,A2:B14, r,INDEX(rng,,1), s,SCAN(1,r,LAMBDA(a,b,IF(b<>OFFSET(b,-1,0),1,a+1))), k,IF(COUNTIF(r,r)=1,0,IF(s=2,0,"")), FILTER(rng,k=0))
Dynamic array formulas.

 

[My Aswer Is This]

Try this:
Asssuming you want the entire row deleted

Sub Test_Me()
'Modified 9/4/2023 4:52 AM DST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row

For i = Lastrow To 1 Step -1
 If Cells(i, 2).Value = 1 Or Cells(i, 2).Value = 3 Then Rows(i).Delete
Next
Application.ScreenUpdating = True
End Sub

 

[LambChoptheKid]

Maybe a formula option:

Book1
	A	B	C	D	E
1	Date Time			Date Time
2	07/01/2023 01:15	a		07/01/2023 01:15	b
3	07/01/2023 01:15	b		07/01/2023 01:30	e
4	07/01/2023 01:15	c		07/01/2023 01:45	h
5	07/01/2023 01:30	d		07/01/2023 02:00	j
6	07/01/2023 01:30	e		07/01/2023 02:15	l
7	07/01/2023 01:30	f
8	07/01/2023 01:45	g
9	07/01/2023 01:45	h
10	07/01/2023 01:45	i
11	07/01/2023 02:00	j
12	07/01/2023 02:15	k
13	07/01/2023 02:15	l
14	07/01/2023 02:15	m
Sheet1

Cell Formulas
Range	Formula
D2:E6	D2	=LET( rng,A2:B14, r,INDEX(rng,,1), s,SCAN(1,r,LAMBDA(a,b,IF(b<>OFFSET(b,-1,0),1,a+1))), k,IF(COUNTIF(r,r)=1,0,IF(s=2,0,"")), FILTER(rng,k=0))
Dynamic array formulas.

I couldn't get this to work. I got an error message saying that my function had too many arguments to function. Thank you though!

 

[LambChoptheKid]

Try this:
Asssuming you want the entire row deleted

Sub Test_Me()
'Modified 9/4/2023 4:52 AM DST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row

For i = Lastrow To 1 Step -1
 If Cells(i, 2).Value = 1 Or Cells(i, 2).Value = 3 Then Rows(i).Delete
Next
Application.ScreenUpdating = True
End Sub

Thanks for the post! Nothing is happening when I run this macro, would I need to modify it to run on my active sheet? I'll keep trying to make this work and post an update if something happens.

Appreciate the help!

 

[My Aswer Is This]

Thanks for the post! Nothing is happening when I run this macro, would I need to modify it to run on my active sheet? I'll keep trying to make this work and post an update if something happens.

Appreciate the help!

Sure, this macro assumes you want to run it on the active sheet. No where in your original post did you mention the specific sheet name.

 

[Georgiboy]

I couldn't get this to work. I got an error message saying that my function had too many arguments to function. Thank you though!

Maybe if we could see a more representative example of your data?