Solved: Delete Rows

[Nicolaf]

Hi,

I have a series of items in different rows and what I need to do is sort them so that there are no duplicates.

So for example:
items origin
row 1 apples france
row 2 pears italy
row 3 bananas costa rica
row 4 pears italy

I need a macro that looks at first item in column and looks if this is repeated in any other row below. If it is repeated it should delete the row which is a duplicate. It should then move to next row and do the same for next item so that in the end all rows will have unique items.

Let's suppose I have four rows range A3:C6

How do I do a macro that leaves rows below?

row 1 apples france
row 2 pears italy
row 3 bananas costa rica

Thanks,
Nic

:doh:

[CatDaddy]

you want them completely removed or hidden? and will there be cases of triplicates?

[georgiboy]

I think another good question to ask is...

You say your range for example is A3:C6 does this mean that you have an item number in colA and a product type in colB and a country code in colC or is all the data in one column?

[Nicolaf]

Completely removed and yes there can be triplicates or more.

first column always item number second column always product type etc.

So I need to look at product type in columnB and if duplicate delete entire row.

Thanks!
Nic

[CatDaddy]

for i=1 to ActiveSheet.UsedRange.Rows.Count
for each cell in Range("B:B")
if cell.value = Range ("B" & i).Value & cell.Address <> Range("B" & i) Then
Rows(i).delete
end if
next cell
next i

[Nicolaf]

I tried macro but all rows get deleted.
I was expecting only last row to get deleted

I pasted data below in range A3:C6

1 applesfrance2 pearsitaly3 bananascosta rica4 pearsitaly

Can you please double-check?

Thanks!
Nik
:think:

[Nicolaf]

How it looks in Excel

1 apples france
2 pears italy
3 bananas costa rica
4 pears italy

[CatDaddy]

Sub RemoveDuplicateRows()
ThisWorkbook.Sheets(1).Activate
Range("A1").Activate
Dim i As Long, lRow As Long
Dim cell As Range
Dim Target
lRow = Range("B65536").End(xlUp).Row
For i = 1 To lRow
Target = ActiveSheet.Range("B" & i).Value

For Each cell In Range("B" & (i + 1) & ":B" & lRow)
If cell.Value = Target Then
cell.EntireRow.Delete
End If
Next cell
Next i
End Sub

Tested and it will work for any amount of rows (less that 665,000 or so at least :) )

[Aussiebear]

Would it be more prudent to look for duplicates by going to the last row used and stepping backwards when comparing?

[CatDaddy]

A lot of people seem to think that but I dont really understand why

[Aussiebear]

Since the initial occurring row is the first instance, all others are then duplicates from a date and time perspective.

[CatDaddy]

so wouldnt it make more sense to have it move down the list?

[Nicolaf]

Thanks now works

:yes

[CatDaddy]

mark solved! :)