Hi anand,

Pasting over existing data doesn't generate an error. However you don't want to paste over existing data I take it. Here's 2 ways to do it.

The first one checks M1 to see if it is blank, then checks Col N, Col O, etc.

The second one looks from Col IV (the last Col) back to the first blank Col. If that is Col M (or less) it uses Col M to start with then Col N, Col O, etc. Which one you use depends on your data layout, etc.

Both require you to select first (as you had it)

Option Explicit
Sub uniqueLoop()
Dim c As Long
    'start at Col M (13)
    c = 13
    'look to right until blank column found
    Do
        'check row 1, Col # is blank
        If Cells(1, c) <> "" Then
            ' not blank, check next col #
            c = c + 1
        Else
            'Col is blank.  Exit Do/Loop
            Exit Do
        End If
    Loop
 
    Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Cells(1, c), unique:=True
End Sub
Sub uniqueLast()
Dim c As Long
    'Find blank Col, looking from last Col to first Col
    c = Range("IV1").End(xlToLeft).Column + 1
 
    'if it is equal to or less than Col M, use Col M
    If c <= 13 Then
        c = 13
    End If
 
    Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Cells(1, c), unique:=True
End Sub

Cheers,

dr