Macro to remove rows where the difference between to cells is more than 0.05

[tonywatsonhelp]

Hi everyone,

I have a sheet of data,

In Column "N" in have last years premium.
In Column "R" in have this years premium.

I want to have a macro remove all the data where the premiums are the same.
but to give a leeway of 0.05 or 5p as last year it might have been sold at £456.95 and this year £456.99 for example

So the macro should do this:

Start at Row2 to last row.
if column "N" = Column "R" give or take 0.05 then delete row

Please help if you can

Thanks

Tony

 

[KennyGreens]

Are you looking for it to compare each cell in the same row between the columns? For example, you are looking to compare cell N5 to cell R5 and if the difference is not within 5% you delete the 2 cells?

 

[Joe4]

I think you have your title backwards:

Macro to remove rows where the difference between to cells is more than 0.05

From your description, it sounds like you want to remove the rows when the difference is less than or equal to 0.05, not greater than.

Assuming that is correct, try this code:

Sub MyRemoveRows()

    Dim lastRow As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column N with data
    lastRow = Cells(Rows.Count, "N").End(xlUp).Row
    
'   Loop through all rows, backwards, up to row 2 (first row of data)
    For r = lastRow To 2 Step -1
'   See if difference is more than 0.05
        If Abs(Round(Cells(r, "N"), 2) - Round(Cells(r, "R"), 2)) <= 0.05 Then
'           Delete row
            Rows(r).Delete
        End If
    Next r
    
    Application.ScreenUpdating = True

End Sub

 

[tonywatsonhelp]

Hi Joe,
You are correct sorry for that
code looks great thanks

Tony

 

[Joe4]

You are welcome.