VBA Remove Duplicates Help

[benjinorth]

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.

Anybody have any solutions that would be great

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

 

[eduzs]

Are you using "K491" or "K" & lastrow?
If lastrow, use debug.print lastrou to see if there's any error retrieving the lastrow value

 

[benjinorth]

Sorry
I want to use "K" & lastrow.
Where would "debug.print lastrow" get placed in the code?

Thanks

 

[eduzs]

just before the line removeduplicate,
see in the immediate window, wich value shows

 

[benjinorth]

unfortunately that didn't work, the same line of code debugs.

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

 

[S_Wish]

you must firstly declarate, what the lastrow is:

lastrow = activesheet.range("a"&activesheet.rows.count).end(xlup).row

 

[benjinorth]

perfect thank you both for your help. works great!!!!