Duplicates - Amend Code

[Excel-ent]

Dear Experts,

I am trying to remove duplicates from Column C. The Range is from C11 - C55. I have the following code, which seems to work perfect when the data starts from A1…. Could you please amend this code or give another code so that my purpose may get fulfilled.


Private Sub CommandButton1_Click()

'Add extra Column, "A" becomes "B"
Columns(1).EntireColumn.Insert

'Filter out duplicates and copy unique list to "A"
Range("B1", Range("B65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True

'Add extra Column, "B" becomes "A"
Columns(2).EntireColumn.Delete

End Sub


Regards

Shan

[DRJ]

Change to this:

Range("B11", Range("B65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("A11"), Unique:=True

[Excel-ent]

Thank you for your reply,

The revision that you have given me again works perfect if the data is in A column, but refuses to work while the data is in C column. My data is in C Column. The column next to it i.e. B & D are not blank, they are filled with other datas. Could you please look at it once again.



Regards,

S h a n

[bolo]

Private Sub CommandButton1_Click()

'Add extra Column, "C" becomes "D"
Columns(3).EntireColumn.Insert

'Filter out duplicates and copy unique list to "C"
Range("D11", Range("D65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("C11"), Unique:=True

'Delete extra Column, "D" becomes "C"
Columns(4).EntireColumn.Delete

End Sub


It is fairly simple to work out. You should try working it out sometimes. The column insert causes column c to become column D. so your sort should be on column D. It then copies the results to column C starting from row 11.

HTH

[Excel-ent]

The code does what is required. but then, why does it reduces the Column width, the original Column width is 24 in Column C, which is the format in all the other sheets as well. After applying the Code, it reduced the Column Width to 8.43.

Can this remain as it is.

Regards,

Shan

[DRJ]

Private Sub CommandButton1_Click()

'Add extra Column, "C" becomes "D"
Columns(3).EntireColumn.Insert

'Filter out duplicates and copy unique list to "C"
Range("D11", Range("D65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("C11"), Unique:=True

'Delete extra Column, "D" becomes "C"
Columns(4).EntireColumn.Delete

Columns("C:C").ColumnWidth = 24

End Sub

[bolo]

It is probably doing that becuase whn you inserted a column you only inserted a column with the default width. Not sure where you can change the size of the defaults though. DRJ's extra line will do it for you though

[Excel-ent]

Hi,

DRJ's Code does the job well ! but why does it delete / remove the contents / heading C9 and C10 whereas filtering starts from C11 .....

Regards,

Shan

[bolo]

The reason why C9 and c10 are deleted is is because the whole column is deleted. What this macro does is:

Step 1: insert an extra column (column C) to work in.
Step 2: filter out the duplicates and copies the results to column C, starting at C11. Note that any headings will not have been copied over.
Step 3: Delete the column of duplicates, also removing the headings.
Step 4: fix the column width

TO keep the headings, assuming they are in C9 and C10 try this:

Private Sub CommandButton1_Click()

'Add extra Column, "C" becomes "D"
Columns(3).EntireColumn.Insert

'Filter out duplicates and copy unique list to "C"
Range("D11", Range("D65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("C11"), Unique:=True

'Copies over the headings from column D.
Range("C9").value = Range("D9").value
Range("C10").value = Range("D10").value

'Delete extra Column, "D" becomes "C"
Columns(4).EntireColumn.Delete

Columns("C:C").ColumnWidth = 24

End Sub

HTH

[Excel-ent]

Hi,

The code do not disturb the Heading NOW. but it remove the fill from the headings which is in yellow colour to White ?

Why after the code is executed, the filtered data remains of the Original fill i.e. light green and other cells (which are duplicates) below changed their fill to light yellow ?


(am i asking 2 much or 3 much) ?

Regards,

Shan