removing duplicates

[wildman]

I have an excel vba script that removes dupes from a spreadsheet. Works fine until more than a few hundred rows are in the sheet. Then it removes some but very few dupes. is there a limit on how much data can go through the advanced filter?
Here is the script..
Sub RemoveDupes()

'sort and remove duplicates
Range("A1").Select

LastRow = Cells.Find(what:="Trailer", LookIn:=xlValues, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

Lastcol = Cells.Find(what:="Trailer", LookIn:=xlValues, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

Lr = LastRow - 1
LstCol = Lastcol - 5
Lc = Chr(LstCol + 64)

FirstCell = "A2"
LastCell = Lc & Lr

myrange = FirstCell & ":" & LastCell
' MsgBox MyRange
Range(myrange).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Range("A1").Select
End Sub

 

[DonkeyOte]

Can you not sort the data first by some criteria - this may make life easier...

Sheets("sheet1").select
Range("a2:f2000").select
Selection.Sort KEY1:=Sheets("sheet1").Range("A2"), ORDER1:=xlascending, HEADER:=xlNo, ORDERCUSTOM:=1, MatchCase:=False, Orientation:=xlTopToBottom

then loop through...

START_ROW = 2
DATARANGE = Sheets("sheet1").range("a2:f2000")

for each ENTRY in DATARANGE

if entry = sheets("sheet1").range("a" & START_ROW + 1) then

range(("a" & START_ROW) & ":" ("f" & START_ROW)).clearcontents

endif

START_ROW = START_ROW + 1

next ENTRY

 

[wildman]

data is allready sorted but I like your code and may try it. perhaps my code is less efficiant or just buggy

 

[wildman]

instaed of just clearing the contents I prefer to hide the cells. like the filter did.

can that be done in the loop as you have described only for the whole row?

 

[DonkeyOte]

You should be able to - substitute the clearcontents for something like EntireRow.Select
Visible = False

Not sure as to the exact code - have a look in help...