removing duplicates

wildman

New Member
Joined
Sep 5, 2002
Messages
6
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

New Member
Joined
Sep 5, 2002
Messages
6
data is allready sorted but I like your code and may try it. perhaps my code is less efficiant or just buggy
 

wildman

New Member
Joined
Sep 5, 2002
Messages
6
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top