Removing Duplicates From Two Columns

[richlocus]

Hello:
I have been fighting for hours trying to simply remove duplicates from a two column worksheet (not an array or table).
Example:
Joe England
Joe England
Mary France
Mary France
Mark England
Mark Germany

After removal it should be:
Joe England
Mary France
Mark England
Mark Germany

All the document I read said it should be in this format:
ActiveSheet.Range("$A$1:$B$6").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo

Since I'm writing in VBA, I used a standard Range notation:
Range(Cells(1,"A"),Cells(6,"B")).RemoveDuplicates Columns:=Array(1,2), Header:=xlNo

It appears that the RemoveDuplicates doesn't like the "Cell" notation. How can I get around this?

Thanks,
Rich Locus

[HansV]

It works for me. Could you test the attached workbook, please?

RemoveDups.xlsm

[richlocus]

Hans:

I'm totally stumped on this one. Using the format provided above, I tried these two iterations:

wksQC_AccountManagerAccountsTable.Range(wksQC_AccountManagerAccountsTable.Cells(2, "A"), wksQC_AccountManagerAccountsTable.Cells(88, "B")).RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo

rngAccountManagerAccountsTable.RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo

Unless I can find a solution, I wrote a quick VBA procedure to remove the duplicates, but I would rather use standard functions.
' ********************************************************
' Remove Duplicates
' ********************************************************
strLastValue1 = "XXX"
strLastValue2 = "XXX"

For i = 2000 To 2 Step -1
If wksQC_AccountManagerAccountsTable.Cells(i, "A") = "" Then
GoTo GetNext
End If

If wksQC_AccountManagerAccountsTable.Cells(i, "A") = strLastValue1 And _
wksQC_AccountManagerAccountsTable.Cells(i, "B") = strLastValue2 Then
wksQC_AccountManagerAccountsTable.Rows(i).EntireRow.Delete
GoTo GetNext
End If

strLastValue1 = wksQC_AccountManagerAccountsTable.Cells(i, "A")
strLastValue2 = wksQC_AccountManagerAccountsTable.Cells(i, "B")

GetNext:
Next i

' **********************************************************************
' Return to Control Page
' **********************************************************************
wksQC_ControlPanel.Activate
wksQC_ControlPanel.Cells(1, "A").Select

End Sub

[HansV]

Did the code in the workbook that I attached work for you?

Could you attach a sample workbook in which the code fails?

[richlocus]

Here is my code, but the workbook is huge. I will try to consolidate it down to a small program. I don't see where to attach any objects in this response. Thanks.

' ********************************************************
' Test - Remove After Test
' ********************************************************
Dim rng As Range
Set rng = wksQC_AccountManagerAccountsTable.Range("A2:B88")
rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo

[richlocus]

Here is my code that failed, but the workbook is huge. I will try to consolidate it down to a small program. I don't see where to attach any objects in this response. Thanks.

' ********************************************************
' Test - Remove After Test
' ********************************************************
Dim rng As Range
Set rng = wksQC_AccountManagerAccountsTable.Range("A2:B88")
rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo

I get this message: Run Time Error 5: Invalid Procedure Call or Argument.

Thanks,
Rich

[HansV]

Make sure that you are in the Full Editor if you want to attach a file.

S2544.png

[richlocus]

Thanks:
I recorded the removal of duplicates available from the Data Menu and it provided the code shown below:
ActiveSheet.Range("$A$2:$B$88").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo

I copied the code, but replace ActiveSheet with the actual worksheet variable since it isn't active at the time.
It still failed with the same error message.

This is really strange! The VBA is almost 4000 lines of code, and this is the only issue I am having.

Rich

[richlocus]

Hans:

I will write a small test program using the same data and worksheet and see what happens. At least I have a workaround. My client is in a hurry to get the program so I will do the test program after I provide the results to my client.

Thanks! I won't forget providing you with the results of the test.

Rich

[richlocus]

Hans:
Attached is a simple example of a two column removal of duplicates. I tried two different formats and it fails in both.

This is sounding like a glitch.
I get a Runtime Error 5 - Invalid Procedure Call or Argument
I didn't bother putting in a button. I just went to the code editor and stepped through it.
Thanks,
Rich Locus

[HansV]

The problem is the line

Code: Select all

Option Base 1

If you remove that, the code will run without error.

[richlocus]

Hans:
I would have never suspected that issue. Thanks!
Rich Locus

[HansV]

And I would never have found it without seeing the workbook...

[p45cal]

You can keep the Option Base 1 with:

Code: Select all

.RemoveDuplicates Columns:=(VBA.Array(1, 2))

that is:
- use VBA.Array instaed of just Array
- enclose the above in parentheses

So too:

Code: Select all

xx = VBA.Array(1, 2)

later:

Code: Select all

.RemoveDuplicates Columns:=(xx),

[HansV]

Thanks, Pascal.

[richlocus]

Thanks again. Another great workaround. Over 4,000 lines of VBA code in the application, and that one row had me against the ropes!!
Hans, Pascal and the team: You are life-savers for developers like myself who make a living doing Excel VBA applications.
Rich Locus