Deleting Rows In Excel 1997-2003 (Ver. 11)

[OldRookie]

I have a range data in A1:E10 with headers in the first row. I want to delete rows that have duplicate data in columns D and E.
In Excel 2010, I’m able to succinctly accomplish this task with:
Range(“A1:E10”).RemoveDuplicates Columns:=Array(4,5),Header:=xlYes

I’m trying to accomplish the same thing in Excel 1997-2003 (Ver.11). My brutal efforts to do so employ For, If, AND statements. Is there something better than that reaches this far back for this version?

 

[Trebor76]

Hi OldRookie,

This worked for me when I saved my workbook in 97-2003 mode:

Option Explicit
Sub RemoveDups()

    Dim strArr() As String
    Dim i As Integer, j As Integer
    Dim rngDelete As Range
    
    Application.ScreenUpdating = False
    
    ReDim strArr(8) 'There are a total of 9 possible data entries (zero based index)
    For i = 2 To 10
        If i = 2 Then
            strArr(j) = Range("D" & i) & Range("E" & i)
            j = j + 1
        Else
            If IsNumeric(Application.Match(Range("D" & i) & Range("E" & i), strArr, 0)) = False Then
                strArr(j) = Range("D" & i) & Range("E" & i)
                j = j + 1
            Else
                If rngDelete Is Nothing Then
                    Set rngDelete = Range("A" & i & ":E" & i)
                Else
                    Set rngDelete = Union(rngDelete, Range("A" & i & ":E" & i))
                End If
            End If
        End If
    Next i
    
    If Not rngDelete Is Nothing Then
        rngDelete.EntireRow.Delete
    End If
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[kevin9999]

Another option which should work with Excel 2003 (unable to test). Just change the sheet name to suit.

Option Explicit
Sub OldRookie()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")       '<-- *** Change to actual sheet name ***
    Dim LRow As Long, LCol As Long, a, b, i As Long, j As Long
    LRow = ws.Cells.Find("*", , xlFormulas, , 1, 2).Row
    LCol = ws.Cells.Find("*", , xlFormulas, , 2, 2).Column + 1
    
    With ws
        With .Range(.Cells(2, LCol), .Cells(LRow, LCol))
            .Formula = "=D2&""|""&E2"
            .Value = .Value
        End With
        For i = LRow To 3 Step -1
            If Application.CountIf(.Range(.Cells(2, LCol), .Cells(i, LCol)), .Cells(i, LCol).Value) > 1 _
            Then .Rows(i).Delete
        Next i
        .Columns(LCol).ClearContents
    End With
    Application.ScreenUpdating = True
End Sub

 

[OldRookie]

My thanks to both of you for your coding. The range A1:E10 in my original description of the problem was selected for ease of communication. The actual problem is considerably larger (Ver11: 2^15 x 21 or Ver12 & higher: 2^19 x 21) but the task is the same. While succinct coding is preferred for its simplicity, I have no reservations against longer coding to accomplish the same thing once it’s debugged. My concern though is run-time. What I’ve written is similar to yours which I consider to be the state-of-the-art at the Ver11 point in time.

A death in the family takes precedence over testing your codes for the immediate future. Still, I need to select one of your suggested codes so this request can be closed out. My untested selection is simply based on fewer For and If statements and nothing else. I’m extremely grateful for both of your responses.
Respectfully,
OldRookie

 

[kevin9999]

Glad we were able to help, and thanks for the feedback.

 

[Trebor76]

A death in the family takes precedence over testing your codes for the immediate future.

Of course. Condolences to the family and our thoughts are with you.

 

[OldRookie]

Thank you for your kindness.