VBA Advanced Filter Question - Definitions needed

[BrianExcel]

Hey folks,

I have a large column of info in column D. BUT, there are several duplicates. What I am trying to do is search for the header text, offset that by one row to indicate where the data set actually starts, then filter the duplicates out, caopy the unique records to a new spot and then name the unique range and populate a combo box with them.

BUT, I am having trouble with the "Advanced Filter" code. For some reason I can't get it working, and I wanted to know if someone could define each component of the code so I understand it better..

Could someone help?

Please describe what each of the following are:

xlFilterCopy vs xlFilterInPlace -

CriteriaRange:=

Copy To Range - I think I understand this part, but how do you indicate a named range or a cell on another sheet if you want to move the results?

Unique -


Thanks in advance.

 

[Andrew Poulsom]

This may help you:

http://www.contextures.com/xladvfilter01_2003.html

 

[Norie]

The options for the first argument kind of do what they say.

xlFilterInPlace - do the filter and show the results in the original location(place)
xlFilterCopy - do the filter and copy the results somewhere else.

You probably want xlFilterCopy.

The 2nd argument is used to specify the range the criteria for the filter is in.

You probably wouldn't need it.

3rd argument, again is what it says - the range you want to copy the results of the filter too.

Unique just tells VBA whether you want to return unique values, so you would want True.

As for specifying a named range for CopyRange, you would do that in the same way you would anywhere else in the code.

So, let's say you want to filter D1:D100 on worksheet 'Data' for duplicates and copy the unique values to a named range called ComboData on sheet 'Lists'.

The code would look something like this:

Set rngSrc = Worksheets("Data").Range("D1:D100")

Set rngDst = Worksheets("Lists").Range("ComboData").Cells(1)
 
rngSrc.AdvancedFilter Action:=xlFilterCopy, , CopyToRange:=rngDst, Unique:=True

Note I've used the first cell in the named range to avoid any errors.