Solved: Looping required

[anandbohra]

Hi all
the below one is my small VBA for extracting UNIQUE in Column M
for the time being i put one error handler
but i want one loop which checks that if value is there in M1 then it should try to paste unique in N1 & so on
means for every error in pasting data it should shift to right column (M, N, O accordingly)


[VBA]Sub unique()
' Keyboard Shortcut: Ctrl+Shift+U
'
On Error GoTo Anand:
Selection.Select
Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("m1" _
), unique:=True
Exit Sub
Anand:
MsgBox "Pl. first Clear the contents of Column M", vbInformation, "Anand M. Bohra"
'Range("B1").Select
End Sub[/VBA]


pl help

[rbrhodes]

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

[anandbohra]

Thank your rbrhodes

you got my query & gave me fabulous reply. it works
I am thankful of you & VBA Express forum which allows me to put my query & get the proper, genuine & required solutions.

bye

[Aussiebear]

Hi Anand, if you consider this thread closed, and I take it that you have a satisfactory solution given your last post, could you please take the time to mark this thread as Solved.

Its just a hint before the Admin people ask you to do so.

[anandbohra]

hi Aussiebear

i didn't got the way as how to "mark this thread as Solved" for that i had searched this entire site for more than 30 minutes even try to find out forum rules but unable to found the same.

can u pl help me out & tell me exactly where that button is situated so that member should devote their precious time in solving other queries.

awaiting for reply.

[Aussiebear]

Sure can. At the top of the page, you can find "Thread Tools". Click on that and as the creator of the thread you will see an option "Mark thread as Solved" or words to that effect.

BTW its nice to see your contribution to this forum.

[anandbohra]

THANKS DUDE

I DID THE SAME NOW IT IS MARKED AS SOLVED

[Aussiebear]

Well there's a first. Never been called DUDE before.

[anandbohra]

is this hurts you