VBA Remove Duplicates Help

benjinorth

New Member
Joined
Jul 25, 2016
Messages
13
Hi All,

I'm using this current code which works fine until i have more than the specified amount of rows,

I tried replacing the highlighted line of code with:
"ActiveSheet.Range("A1:K" & Lastrow).RemoveDuplicates Columns:=2, Header:=xlYes"

but I get an error. :mad:

Anybody have any solutions that would be great

Code:
Sub ClearDups()


Worksheets("Copy Me Here").Select
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add Key _
        :=Range("F1:F137"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
        
    With ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
[I][B][COLOR=#0000cd]    ActiveSheet.Range("A1:K491").RemoveDuplicates Columns:=2, Header:=xlYes[/COLOR][/B][/I]
    Sheets("MAIN PAGE").Select
    
End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Are you using "K491" or "K" & lastrow?
If lastrow, use debug.print lastrou to see if there's any error retrieving the lastrow value
 
Last edited:
Upvote 0
just before the line removeduplicate,
see in the immediate window, wich value shows
 
Upvote 0
unfortunately that didn't work, the same line of code debugs.

Code:
Sub ClearDups()

    Sheets("Copy Me Here").Select
    ActiveWorkbook.Worksheets("Copy Me Here").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Copy Me Here").AutoFilter.Sort.SortFields.Add Key _
        :=Range("F1:F137"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal


    With ActiveWorkbook.Worksheets("Copy Me Here").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Debug.Print lastrow
[COLOR=#ff0000][I]    ActiveSheet.Range("A1:K" & lastrow).RemoveDuplicates Columns:=2, Header:=xlYes[/I][/COLOR]
    Sheets("MAIN PAGE").Select


End Sub
 
Upvote 0
you must firstly declarate, what the lastrow is:
Code:
lastrow = activesheet.range("a"&activesheet.rows.count).end(xlup).row
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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
Back
Top