PDA

View Full Version : Solved: Looping required



anandbohra
06-08-2007, 05:03 AM
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)


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


pl help

rbrhodes
06-10-2007, 06:52 PM
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
06-10-2007, 10:12 PM
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
06-10-2007, 11:06 PM
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
06-10-2007, 11:33 PM
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
06-11-2007, 12:00 AM
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.:clap:

anandbohra
06-11-2007, 12:03 AM
THANKS DUDE

I DID THE SAME NOW IT IS MARKED AS SOLVED

Aussiebear
06-11-2007, 01:55 AM
Well there's a first. Never been called DUDE before.

anandbohra
06-11-2007, 04:21 AM
is this hurts you