Removing Duplicates From Two Columns

richlocus
2StarLounger
Posts: 168
Joined: 03 Oct 2015, 00:30

Removing Duplicates From Two Columns

Post by 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

User avatar
HansV
Administrator
Posts: 79539
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Removing Duplicates From Two Columns

Post by HansV »

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

RemoveDups.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

richlocus
2StarLounger
Posts: 168
Joined: 03 Oct 2015, 00:30

Re: Removing Duplicates From Two Columns

Post by 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

User avatar
HansV
Administrator
Posts: 79539
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Removing Duplicates From Two Columns

Post by HansV »

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

Could you attach a sample workbook in which the code fails?
Best wishes,
Hans

richlocus
2StarLounger
Posts: 168
Joined: 03 Oct 2015, 00:30

Re: Removing Duplicates From Two Columns

Post by 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
2StarLounger
Posts: 168
Joined: 03 Oct 2015, 00:30

Re: Removing Duplicates From Two Columns

Post by 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

User avatar
HansV
Administrator
Posts: 79539
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Removing Duplicates From Two Columns

Post by HansV »

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

S2544.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

richlocus
2StarLounger
Posts: 168
Joined: 03 Oct 2015, 00:30

Re: Removing Duplicates From Two Columns

Post by 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
2StarLounger
Posts: 168
Joined: 03 Oct 2015, 00:30

Re: Removing Duplicates From Two Columns

Post by 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
2StarLounger
Posts: 168
Joined: 03 Oct 2015, 00:30

Re: Removing Duplicates From Two Columns

Post by 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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 79539
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Removing Duplicates From Two Columns

Post by HansV »

The problem is the line

Code: Select all

Option Base 1
If you remove that, the code will run without error.
Best wishes,
Hans

richlocus
2StarLounger
Posts: 168
Joined: 03 Oct 2015, 00:30

Re: Removing Duplicates From Two Columns

Post by richlocus »

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

User avatar
HansV
Administrator
Posts: 79539
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Removing Duplicates From Two Columns

Post by HansV »

And I would never have found it without seeing the workbook...
Best wishes,
Hans

User avatar
p45cal
2StarLounger
Posts: 169
Joined: 11 Jun 2012, 20:37

Re: Removing Duplicates From Two Columns

Post by 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),

User avatar
HansV
Administrator
Posts: 79539
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Removing Duplicates From Two Columns

Post by HansV »

Thanks, Pascal.
Best wishes,
Hans

richlocus
2StarLounger
Posts: 168
Joined: 03 Oct 2015, 00:30

Re: Removing Duplicates From Two Columns

Post by 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