Eliminating duplicate data

Question

I'm a new user in VBA and I wrote the following basic code in order to eliminate duplicate data in one sheet and extract unique data into another sheet.

For example:

in Sheet1 there are total number of 184 cells which contains the same date (12/07/2007) in Column A.
I desire to take one of that date data and write it to a cell in Sheet2.
Therefore, there will be 12/07/2007 written in A1 in Sheet2.

I would appreciate any kind of help.

Code:

Sub Take_Unique() Workbooks("historicaldata.xls").Activate Dim i As String Dim xrow As Long xrow = 2 Do Until Sheet1.Cells(xrow, 1) = "" If Sheet1.Cells(xrow, 1).Value = Sheet1.Cells(xrow + 1, 1) Then Sheet12.Cells(xrow, 1).Value = Sheet1.Cells(xrow, 1) End If xrow = xrow + 1 Loop End Sub

It just eliminates one of the date data and 183 of them still remains. — Volkan Demir
– Volkan Demir, Commented Jun 9, 2016 at 10:38
Why not just use either the Remove Duplicates feature or the Advanced Filter? — Ron Rosenfeld
– Ron Rosenfeld, Commented Jun 9, 2016 at 10:40
Thanks! However, it is going to be a part of a longer code. So, VBA usage is necessary for me. — Volkan Demir
– Volkan Demir, Commented Jun 9, 2016 at 10:47

Community · Accepted Answer · 2021-01-18 12:30:08Z

2

Another approach is my duplicate master addin.

It handles whitespace, case sensitivities and even regexp matches - so it goes beyond the default comparison.

edited Jan 18, 2021 at 12:30

CommunityBot

11 silver badge

answered Jun 10, 2016 at 3:46

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave · Accepted Answer · 2016-06-09 10:41:16Z

Simpler to just copy the whole column from sheet1 to sheet12:

Sheet1.Columns(1).Copy Destination:=Sheet12.Columns(1)

Then use the RemoveDuplicates functionality to drop any duplicates (set the Header if it's present and not if it isn't)

Sheet12.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlYes

Collectives™ on Stack Overflow

Eliminating duplicate data

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related