Removing Duplicates From Two Columns
-
- 2StarLounger
- Posts: 168
- Joined: 03 Oct 2015, 00:30
Removing Duplicates From Two Columns
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
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
-
- Administrator
- Posts: 79539
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Removing Duplicates From Two Columns
It works for me. Could you test the attached workbook, please?
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 168
- Joined: 03 Oct 2015, 00:30
Re: Removing Duplicates From Two Columns
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
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
-
- Administrator
- Posts: 79539
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Removing Duplicates From Two Columns
Did the code in the workbook that I attached work for you?
Could you attach a sample workbook in which the code fails?
Could you attach a sample workbook in which the code fails?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 168
- Joined: 03 Oct 2015, 00:30
Re: Removing Duplicates From Two Columns
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
' ********************************************************
' Test - Remove After Test
' ********************************************************
Dim rng As Range
Set rng = wksQC_AccountManagerAccountsTable.Range("A2:B88")
rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
-
- 2StarLounger
- Posts: 168
- Joined: 03 Oct 2015, 00:30
Re: Removing Duplicates From Two Columns
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
' ********************************************************
' 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
-
- Administrator
- Posts: 79539
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Removing Duplicates From Two Columns
Make sure that you are in the Full Editor if you want to attach a file.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 168
- Joined: 03 Oct 2015, 00:30
Re: Removing Duplicates From Two Columns
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
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
-
- 2StarLounger
- Posts: 168
- Joined: 03 Oct 2015, 00:30
Re: Removing Duplicates From Two Columns
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
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
-
- 2StarLounger
- Posts: 168
- Joined: 03 Oct 2015, 00:30
Re: Removing Duplicates From Two Columns
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
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.
-
- Administrator
- Posts: 79539
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Removing Duplicates From Two Columns
The problem is the line
If you remove that, the code will run without error.
Code: Select all
Option Base 1
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 168
- Joined: 03 Oct 2015, 00:30
Re: Removing Duplicates From Two Columns
Hans:
I would have never suspected that issue. Thanks!
Rich Locus
I would have never suspected that issue. Thanks!
Rich Locus
-
- Administrator
- Posts: 79539
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Removing Duplicates From Two Columns
And I would never have found it without seeing the workbook...
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 169
- Joined: 11 Jun 2012, 20:37
Re: Removing Duplicates From Two Columns
You can keep the Option Base 1 with:
that is:
- use VBA.Array instaed of just Array
- enclose the above in parentheses
So too:
later:
Code: Select all
.RemoveDuplicates Columns:=(VBA.Array(1, 2))
- use VBA.Array instaed of just Array
- enclose the above in parentheses
So too:
Code: Select all
xx = VBA.Array(1, 2)
Code: Select all
.RemoveDuplicates Columns:=(xx),
-
- Administrator
- Posts: 79539
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 168
- Joined: 03 Oct 2015, 00:30
Re: Removing Duplicates From Two Columns
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
Hans, Pascal and the team: You are life-savers for developers like myself who make a living doing Excel VBA applications.
Rich Locus