Delete Duplicates with three more requirements

albita09

New member
Joined
Jan 30, 2014
Messages
13
Reaction score
0
Points
0
Dear all here,

First of all thank you very much for your previous help!! This Forum has been and is still of a great help for me and I am learning a lot thank to you!:kiss:

I am asking now regarding a VBA (Macro code) for Deleting Duplicates :D

There is an option in Excel ("Remove Duplicates") and it does it in the number of cells selected.
It also removes the duplicate the is in the lower position.

Is there a VBA code, or Macro that
  1. DELETES DUPLICATES
  2. FROM TWO COLUMNS
  3. ONLY OF THE SELECTED CELLS
  4. AND CHOOSING WHICH OF THE TWO I WOULD LIKE TO REMOVE (upper or lower)?

THANK YOU VERY MUCH!

For example, I would like to remove duplicates of columns 2 and 3. Therefore Anna and Dandys lines are duplicates and Solomon and Kisas lines are also duplicates (Dandy and Kisas' lines would be deleted)
column1column2column3
Anna
00101
Martha00202
Solomon00602
Dandy001
01
Patricia00301
Frank00403
Sarah
00404
Laurent00505
Kisa006
02
 

albita09

New member
Joined
Jan 30, 2014
Messages
13
Reaction score
0
Points
0
Dear Excelforum community!

I am just checking whether this message has reached to you...

I would really appreciate help in this or, if not possible, a hint where to find it.

Thank you once more!
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
Albita09 how do you determine what record to keep?
In your example it it just happens to be, keep the first record, the next one that is a duplicate is deleted.
Are there only ever going to be 2 duplicates? For your choice to pick to keep the upper or lower?
Do you manually want to watch this run and choose every time it catches a duplicate, or create a list of the duplicate values to view and manually copy later?
Any other criteria would be helpful, as the easiest way to do this will be to sort the entire list by column 2 and 3 to do comparisons.
 

albita09

New member
Joined
Jan 30, 2014
Messages
13
Reaction score
0
Points
0
Dear Simi,

First of all thank you very much for your answer!

Actually, I need it much easier. The might be more than 1 duplicate of some elements, however I am TOTALLY OK when we keep the upper row ("Remove Duplicates" Excel Button does that") Therefore we could actually forget about condition number 4.

Actually what I would like the Macro to do is to run EXACTLY what the button " Remove Duplicates" does. But not going manually with the mouse to the button but running it with a macro. (The idea behing is to merge this macro later on with other two and get a whole process run at once)

Is it clear now? Do you want me to re-explain anything?

Thank you VERY VERY VERY MUCH or your help in advance!

A.
 

albita09

New member
Joined
Jan 30, 2014
Messages
13
Reaction score
0
Points
0
Dear Simi,

Once again, I have been doing some research online and this is the furhter I have gotten:

Sub Macro1()
'
' Macro1 Macro
'
Dim count
For count = Selection.Row To Selection.Row + Selection.Rows.count - 1
'
Range("C" & count).Select
ActiveSheet.Range("C" & count).RemoveDuplicates Columns:=Array(2, 3), Header _
:=xlNo

Next
End Sub

I am selecting some rows of column "C" but I ignore the number therefore I would like the macro to run for all the selected ones. A macro will do two more steps before the removing duplicates.
The third step is this one and I would like the Macro to REMOVE DUPLICATES as per columns "D" and "E", eliminating the WHOLE.
Exactly as the button "Removing Duplicates" does.

This code is a copy paste of what I get recording the Macro that results of running it with the button however it always uses definite columns and rows so I ahve use the line

'
Dim count
For count = Selection.Row To Selection.Row + Selection.Rows.count - 1

That in other macros helped me to run things on the selected cells.

However I have tried to run this code (that is for sure worng) and I get the Runtime Error '5' Invalid procedure call or argument

Since I do not know how to rpogram in VBA I wonder if giving you this first research would be easier to help me...

Thank you!!!
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
Albita09

To accomplish what you want you need only the following.

Code:
Sub Simi_Remove_duplicates()
    ActiveSheet.Range(Cells(Selection.Row, Selection.Column), Cells(Selection.Row + Selection.Rows.count, Selection.Column + Selection.Columns.count)).RemoveDuplicates Columns:=Array(2, 3), Header _
        :=xlNo
End Sub

to remove the duplicates in the fashion you want you needed to select more than just column "C". I am simply using the selection properties to identify the range by cell number, instead of cell name. cells(1,1) refers to cell A1. cells(1,2) refers to cell B2.
 

albita09

New member
Joined
Jan 30, 2014
Messages
13
Reaction score
0
Points
0
That is great Simi!

Its seems to work perfectly!

However you are right. I might no be able to incorporated of a bigger flow of macro this one nees to select all of the columns.

I am going to do some more tires and I will get back to you if any questions!

THANK YOU!
 
Top