Macro to delete row when value of two cells is not equal

[Jibroni]

Hello,

I have the following table and need a macro that will delete the entire row if the values in columns E (Remaining) and F (Saba Remaining TUs) is not equal. So in this example here:

ID18 - Training	Training: Training	Account Name	Expiration Date	Remaining	Saba Remaining TUs
a150h000009XncWAAS	TR2104-7532	x	7/15/2022	36	36​
a150h000009Xqc1AAC	TR2104-7550	xx	7/23/2022	40	30​
a150h000009XpJ3AAK	TR2104-7543	xxx	7/20/2022	30	40​
a150h000009XncgAAC	TR2104-7534	xxxx	7/15/2022	28	28​

Rows 3 and 4 would be deleted because their values do not match.

Additional note: values in columns E and F are always numeric.

Is there a basic macro that would do this? Or maybe even a formula?
Thank you!

 

[mumps]

Try:

Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long, x As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x = LastRow To 2 Step -1
        If Cells(x, 5) <> Cells(x, 6) Then
            Rows(x).Delete
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

[Jibroni]

Try:

Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long, x As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x = LastRow To 2 Step -1
        If Cells(x, 5) <> Cells(x, 6) Then
            Rows(x).Delete
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

Hi @mumps, thanks for the quick reply. I tested your macro on the example table from my original post and it deleted the rows where the values are not equal, but also deleted the 2nd row where the values are equal. Any idea why it would delete that row? Appreciate your help!

 

[mumps]

I had the same problem when I first tested the macro. It looks like the values in column F have an invisible question at the end so F2 displays as 36 but in reality is 36? This would make all the values different. Are you importing the data from an external source?

 

[Jibroni]

I had the same problem when I first tested the macro. It looks like the values in column F have an invisible question at the end so F2 displays as 36 but in reality is 36? This would make all the values different. Are you importing the data from an external source?

Ah, thanks for catching that. Yes, I am importing the data. So that makes sense. Your macro does the trick, I just need to investigate the data from the import first, before I run the macro. Thank you for your help!

 

[mumps]

You are very welcome.

 

[johnnyL]

Here is an alternative approach that doesn't require a loop:

Sub TestDelete()
'
    Dim LastRowInColumn As Long
'
    LastRowInColumn = Range("E" & Rows.Count).End(xlUp).Row
'
    Application.ScreenUpdating = False
'
    On Error GoTo NoDeletes
    With Range("E2:E" & LastRowInColumn)
        .Value = Evaluate("If(" & .Address & "-" & .Offset(0, 1).Address & "<>0,""#N/A""," & .Address & ")")
        .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    End With
'
NoDeletes:
    Application.ScreenUpdating = True
End Sub

 

[Jibroni]

Here is an alternative approach that doesn't require a loop:

Sub TestDelete()
'
    Dim LastRowInColumn As Long
'
    LastRowInColumn = Range("E" & Rows.Count).End(xlUp).Row
'
    Application.ScreenUpdating = False
'
    On Error GoTo NoDeletes
    With Range("E2:E" & LastRowInColumn)
        .Value = Evaluate("If(" & .Address & "-" & .Offset(0, 1).Address & "<>0,""#N/A""," & .Address & ")")
        .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    End With
'
NoDeletes:
    Application.ScreenUpdating = True
End Sub

Ah, this also does the trick. Thank you!

 

[johnnyL]

You are welcome.

Not sure how many rows you are using this on, but if you had a large amount of rows to check, the no loop version would probably perform faster.