Macro to delete a row and the row immediately under it, if a specific condition is met

[missdys]

Good afternoon! I am hoping I can get some guidance on creating a macro. I have a spreadsheet that consists of over 10k lines. I want to write a macro that will locate any cell in column b that contains "xxxx-825" and deletes the line. But when it deletes that line, I also want it to delete the line directly below it is the data in column A matches. A sample of my data is below to help understand what I am working with. I appreciate any help I can get with this!![TABLE="width: 672"]
<tbody>[TR]
[TD]vch_int_id
[/TD]
[TD]acc_no
[/TD]
[TD]acc_ds
[/TD]
[TD]acc_at
[/TD]
[TD]gl_pst_dt
[/TD]
[TD]jnl_int_id
[/TD]
[/TR]
[TR]
[TD="align: right"]255673
[/TD]
[TD]1011-001
[/TD]
[TD]FIRST CITIZENS-OPERATING FUND
[/TD]
[TD="align: right"]-62238.76
[/TD]
[TD="align: right"]09/03/2014 0:00
[/TD]
[TD="align: right"]84358
[/TD]
[/TR]
[TR]
[TD="align: right"]255673
[/TD]
[TD]2020-000
[/TD]
[TD]ACCOUNTS PAYABLE
[/TD]
[TD="align: right"]62238.76
[/TD]
[TD="align: right"]09/03/2014 0:00
[/TD]
[TD="align: right"]84358
[/TD]
[/TR]
[TR]
[TD="align: right"]256754
[/TD]
[TD]1011-001
[/TD]
[TD]FIRST CITIZENS-OPERATING FUND
[/TD]
[TD="align: right"]-76664.64
[/TD]
[TD="align: right"]09/03/2014 0:00
[/TD]
[TD="align: right"]84358
[/TD]
[/TR]
[TR]
[TD="align: right"]256754
[/TD]
[TD]2020-000
[/TD]
[TD]ACCOUNTS PAYABLE
[/TD]
[TD="align: right"]76664.64
[/TD]
[TD="align: right"]09/03/2014 0:00
[/TD]
[TD="align: right"]84358
[/TD]
[/TR]
[TR]
[TD="align: right"]257757
[/TD]
[TD]7010-825
[/TD]
[TD]LABG MEDICAL SPECIALIST FEES
[/TD]
[TD="align: right"]-14205.81
[/TD]
[TD="align: right"]09/11/2014 10:41
[/TD]
[TD="align: right"]84783
[/TD]
[/TR]
[TR]
[TD="align: right"]257757
[/TD]
[TD]2020-000
[/TD]
[TD]ACCOUNTS PAYABLE
[/TD]
[TD="align: right"]14205.81
[/TD]
[TD="align: right"]09/11/2014 10:41
[/TD]
[TD="align: right"]84783
[/TD]
[/TR]
[TR]
[TD="align: right"]258296
[/TD]
[TD]1011-001
[/TD]
[TD]FIRST CITIZENS-OPERATING FUND
[/TD]
[TD="align: right"]-95.54
[/TD]
[TD="align: right"]09/03/2014 0:00
[/TD]
[TD="align: right"]84358
[/TD]
[/TR]
[TR]
[TD="align: right"]258296
[/TD]
[TD]2020-000
[/TD]
[TD]ACCOUNTS PAYABLE
[/TD]
[TD="align: right"]95.54
[/TD]
[TD="align: right"]09/03/2014 0:00
[/TD]
[TD="align: right"]84358
[/TD]
[/TR]
[TR]
[TD="align: right"]258297
[/TD]
[TD]1011-001
[/TD]
[TD]FIRST CITIZENS-OPERATING FUND
[/TD]
[TD="align: right"]-112.78
[/TD]
[TD="align: right"]09/03/2014 0:00
[/TD]
[TD="align: right"]84358
[/TD]
[/TR]
[TR]
[TD="align: right"]258297
[/TD]
[TD]2020-000
[/TD]
[TD]ACCOUNTS PAYABLE
[/TD]
[TD="align: right"]112.78
[/TD]
[TD="align: right"]09/03/2014 0:00
[/TD]
[TD="align: right"]84358
[/TD]
[/TR]
[TR]
[TD="align: right"]258298
[/TD]
[TD]1011-001
[/TD]
[TD]FIRST CITIZENS-OPERATING FUND
[/TD]
[TD="align: right"]-296.7
[/TD]
[TD="align: right"]09/03/2014 0:00
[/TD]
[TD="align: right"]84358
[/TD]
[/TR]
[TR]
[TD="align: right"]258298
[/TD]
[TD]2020-000
[/TD]
[TD]ACCOUNTS PAYABLE
[/TD]
[TD="align: right"]296.7
[/TD]
[TD="align: right"]09/03/2014 0:00
[/TD]
[TD="align: right"]84358
[/TD]
[/TR]
[TR]
[TD="align: right"]258477
[/TD]
[TD]1011-001
[/TD]
[TD]FIRST CITIZENS-OPERATING FUND
[/TD]
[TD="align: right"]-600.56
[/TD]
[TD="align: right"]09/12/2014 0:00
[/TD]
[TD="align: right"]84804
[/TD]
[/TR]
[TR]
[TD="align: right"]258477
[/TD]
[TD]2020-000
[/TD]
[TD]ACCOUNTS PAYABLE
[/TD]
[TD="align: right"]600.56
[/TD]
[TD="align: right"]09/12/2014 0:00
[/TD]
[TD="align: right"]84804
[/TD]
[/TR]
[TR]
[TD="align: right"]258504
[/TD]
[TD]1011-001
[/TD]
[TD]FIRST CITIZENS-OPERATING FUND
[/TD]
[TD="align: right"]-78665.87
[/TD]
[TD="align: right"]09/03/2014 0:00
[/TD]
[TD="align: right"]84358
[/TD]
[/TR]
[TR]
[TD="align: right"]258504
[/TD]
[TD]2020-000
[/TD]
[TD]ACCOUNTS PAYABLE
[/TD]
[TD="align: right"]78665.87
[/TD]
[TD="align: right"]09/03/2014 0:00
[/TD]
[TD="align: right"]84358
[/TD]
[/TR]
[TR]
[TD="align: right"]258560
[/TD]
[TD]1011-001
[/TD]
[TD]FIRST CITIZENS-OPERATING FUND
[/TD]
[TD="align: right"]-268.26
[/TD]
[TD="align: right"]09/03/2014 0:00
[/TD]
[TD="align: right"]84358
[/TD]
[/TR]
[TR]
[TD="align: right"]258560
[/TD]
[TD]2020-000
[/TD]
[TD]ACCOUNTS PAYABLE
[/TD]
[TD="align: right"]268.26
[/TD]
[TD="align: right"]09/03/2014 0:00
[/TD]
[TD="align: right"]84358
[/TD]
[/TR]
[TR]
[TD="align: right"]258613
[/TD]
[TD]1011-001
[/TD]
[TD]FIRST CITIZENS-OPERATING FUND
[/TD]
[TD="align: right"]-618.71
[/TD]
[TD="align: right"]09/03/2014 0:00
[/TD]
[TD="align: right"]84358
[/TD]
[/TR]
[TR]
[TD="align: right"]258613
[/TD]
[TD]2020-000
[/TD]
[TD]ACCOUNTS PAYABLE
[/TD]
[TD="align: right"]618.71
[/TD]
[TD="align: right"]09/03/2014 0:00
[/TD]
[TD="align: right"]84358
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Beyond_avarice]

If I find the time I will create the macro. I however wanted to at least give you a quick non VBA solution.

Insert the following formula(this assumes that account number will always start in B2):

=IF(OR(ISNUMBER(FIND("825",B2)),NOT(ISERROR(FIND("825",B1)))), TRUE,FALSE)

Filter out the "False" results. Select all rows that gave a "True" result. Right click and "Delete." Remove the filter.

 

[JLGWhiz]

Try this:

Sub delstuff()
Dim sh As Worksheet, lr As Long
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr - 1 To 2 Step -1
        With sh
            If Right(Trim(.Cells(i, 2).Value), 3) = 825 And _
            Trim(.Cells(i, 1).Value) = Trim(.Cells(i + 1, 1).Value) Then
                Cells(i, 1).Resize(2, 1).EntireRow.Delete
            End If
        End With
    Next
End Sub

 

[missdys]

Thank you for that formula! I was thinking an If statement would help identify the items a let me filter then delete! This was a huge help!

 

[missdys]

JLG! THANK YOU!!! That is awesome! That worked beautifully!

 

[JLGWhiz]

JLG! THANK YOU!!! That is awesome! That worked beautifully!

You are welcome,
Regards, JLG