Copy Unique records to another location

[K. Georgiadis]

I'm betting that there is a way to use VBA to emulate Excel's built-in Advanced Filtering capability:

I have a data list in A5:L167, row 5 being the row that contains the column headings. Each row is a record. Some records are duplicates or triplicates. I want to copy unique records only to a new worksheet (to be created upon running the procedure) to be titled "Filtered Records."

Any suggestions?

[Ken Puls]

So... the advanced filter does do what you need, you just would rather run it at the click of a button?

Why not just record a macro to do it?

[K. Georgiadis]

the thought has occurred to me, even though recorded macros tend to carry a lot of overhead

[Anne Troy]

Post the recorded macro code, and we'll show you what can be removed.

[K. Georgiadis]

sounds fair!

the recorded macro looks pretty economical to me even though I don't think that the last two lines of the recorded code do anything useful.

What I did find out is that Advanced Filtering will not allow copying to a new worksheet; it will only allow copying to a range in the active worksheet.
Here is the recorded macro:

Sub copy_UniqueRecords()
' copy_UniqueRecords Macro
' Macro recorded 3/6/2005 by KG
Range("A5:L167").Select
Range("A5:L167").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"A5:L167"), CopyToRange:=Range("A169"), Unique:=True
ActiveWindow.SmallScroll Down:=153
Range("E174").Select
End Sub

I can go with this!

[Anne Troy]

Perhaps drj's new kb entry will help? I asked for it.

See if you can maybe see how he's copying rows to new sheets.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=318

I'm no coder, so I can't check out what you recorded, but others can. It also gives them ranges to work with and such when helping to work out yours. Uploading a sample file is always real sweet, too. Good luck!

[K. Georgiadis]

thanks!!!

[Anne Troy]

It was just a coinkydink, really. I asked for it like two days ago.

And most of my input here is for clarification, which can help to save the *real* coders' time for coding.

So come on back if you can't work it out. A real coder will jump in eventually!

[Ken Puls]

Hey there,

Using your code, try this out:

Sub copy_UniqueRecords() 
      Range("A5:L167").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ 
    "A5:L167"), CopyToRange:=Range("A169"), Unique:=True 
    Range("E174").Select 
End Sub

You could possibly get rid of the Range("E174").select as well, depending on if you want to go to that cell.

[K. Georgiadis]

and your input is greatly appreciated. I am also evaluating a (non-VBA) technique posted by Debra Dalgleish of www.contextures.com. I ran into an initial snag but I'm not giving up!

Hi Ken,

I eliminated the last line of code without detriment. Thanks for your help.

yes, Debra Dalgleish's "non-VBA" technique does work. For those who are interested, here is the link:

http://www.contextures.com/xladvfilter01.html#ExtractWs

there are two keys:

1. the new worksheet where the unique records are to be copied to must be created before the Advanced Filter is activated
2. the destination worksheet must be the active worksheet when the Advanced Filter is launched, because Excel will only copy filtered data to the active sheet