View Full Version : Solved: Delete Rows
Nicolaf
07-13-2011, 10:40 AM
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
07-13-2011, 10:42 AM
you want them completely removed or hidden? and will there be cases of triplicates?
georgiboy
07-13-2011, 11:13 PM
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
07-14-2011, 01:29 AM
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
07-14-2011, 10:32 AM
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
07-15-2011, 09:00 AM
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
07-15-2011, 10:03 AM
How it looks in Excel
1 apples france
2 pears italy
3 bananas costa rica
4 pears italy
CatDaddy
07-15-2011, 10:51 AM
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
07-15-2011, 03:43 PM
Would it be more prudent to look for duplicates by going to the last row used and stepping backwards when comparing?
CatDaddy
07-15-2011, 03:59 PM
A lot of people seem to think that but I dont really understand why
Aussiebear
07-15-2011, 04:16 PM
Since the initial occurring row is the first instance, all others are then duplicates from a date and time perspective.
CatDaddy
07-15-2011, 04:21 PM
so wouldnt it make more sense to have it move down the list?
Nicolaf
07-18-2011, 10:03 AM
Thanks now works
:yes
CatDaddy
07-18-2011, 10:41 AM
mark solved! :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.