Delete rows if a cell value is less than another cell value

[simonarcher]

Hi.
I have table with a varying number of rows. (20000+)
If the cell in Col D is less than Col F I want the row deleted and so on until only rows where Col D is greater then Col F remain.
Can anyone help a learner like me with some macro code?
Many thanks in advance.
Simon

 

[Trebor76]

Hi Simon,

Here's one way that deletes the rows backwards to Row 2 (just try it initially on a copy of your data in case the results are not as expected):

Sub Macro1()

    Dim lngLastRow As Long, _
        lngActiveRow As Long
    
    If WorksheetFunction.CountA(ActiveSheet.Cells) > 0 Then
    
        lngLastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
    Else
        
        MsgBox "There is no data on " & ActiveSheet.Name & "!!", vbExclamation, "Delete Row Editor"
        Exit Sub
                     
    End If
    
    Application.ScreenUpdating = False

    For lngActiveRow = lngLastRow To 2 Step -1
        If Range("D" & lngActiveRow).Value < Range("F" & lngActiveRow).Value Then
            Rows(lngActiveRow).EntireRow.Delete
        End If
    Next lngActiveRow
    
    Application.ScreenUpdating = True
    
End Sub

HTH

Robert

 

[Michael M]

Hi Simon and Welcome to the Board
What happens if D is = to F ??
For your original question, try this

Sub dlrow()
Dim lr As Long, r As Long
lr = Range("D" & Rows.Count).End(xlUp).Row
    For r = lr To 1 Step -1
        If Range("D" & r).Value < Range("F" & r).Value Then Rows(r).Delete
    Next r
End Sub

If you need to allow for < or = to then use this one

Sub dlrow()
Dim lr As Long, r As Long
lr = Range("D" & Rows.Count).End(xlUp).Row
    For r = lr To 1 Step -1
        If Range("D" & r).Value < = Range("F" & r).Value Then Rows(r).Delete
    Next r
End Sub