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