Activesheet.paste

[Midwinter]

Hi there,

I'm new to all this as you will be able to tell

What I am trying to do is select the values in a column and remove the duplicates. I recorded a macro and then edited it to suit me. The piece that is failing is ActiveSheet.Paste. I have already tried ActiveSheet.PasteSpecial as well as changing it to Select.Range("F1") followed by Selection.Paste and Selection.pastespecial. It still fails with the error 1004.

Thanks for any help.

 Range("F2").Select
        If Selection.Value <> "" Then
            Columns("F:F").Select
            Range("F2:F2000").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
            Selection.Copy
            Application.CutCopyMode = False
            ActiveSheet.ShowAllData
            Selection.ClearContents
            ActiveSheet.Paste
        End If

[shg]

Welcome to the forum.

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

Thanks.

[JBeaucaire]

If you want to delete the duplicates, leaving one of each of the unique values and base that on the values in column F, I would do this in an empty column and the AutoFilter.

Option Explicit

Sub DeleDupesByColumnF()
Dim LC As Long, LR As Long

'Last row and last column
    LR = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LC = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column + 2

'Autofilter key column
    Cells(1, LC) = "key"
    Range(Cells(2, LC), Cells(LR, LC)).FormulaR1C1 = "=ISNUMBER(MATCH(RC6,R1C6:R[-1]C6,0))"
    Columns(LC).AutoFilter
    Columns(LC).AutoFilter Field:=1, Criteria1:="TRUE"
    Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete xlShiftUp
    

'cleanup
    ActiveSheet.AutoFilterMode = False
    Columns(LC).ClearContents
End Sub

[Midwinter]

Thanks heaps. I'll give it a go

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.