code to check data for dupes and remove

[redspanna]

Hi all

My sheet has data in columns ABC thru to line 21000

Can somebody help with code that will check each value thru column A and if any dupes are fould remove that row

for example if A25 shows THE VALUE '512' and it is already shown in A12 then delete row 25, loop until all rows have been checked

result would be aprox 100 rows of unique data where the value in column A is unique.

any help appreciated

many thanks

 

[SteveO59L]

=COUNTIF($A$1:A1,A1)

copied through your range, will return the number of times a duplicate is found in col A. Simply filter for values>1 and delete those rows. No need for VBA


Alternatively, Pivot Tables and Advanced Filter will produce lists of unique values.

 

[pushkardey]

Hi redspanna,

i guess you can use the following code....

Sub removeduplicates()
k = Application.WorksheetFunction.CountA(ActiveSheet.Range("A:A"))
Columns("A:C").Select
ActiveSheet.Range("$A$1:$C$" & k).removeduplicates Columns:=1, Header:=xlNo
End Sub

I have made the folowing assumptions.
1. your data is starting from A1
2. there are no blanks in between.
3. there are no headers. ( if there is a header use Header:=xlYes in place of Header:=xlNo)
4. " if A25 shows THE VALUE '512' and it is already shown in A12 then delete row 25" so remove entire riow if a has duplicates...


hope i could help...

 

[redspanna]

Thank you both so much, both solutions worked

regards