How do I get a list of unique values from a column in Excel VBA?
I've gone through many different articles that filter unique values; however almost all of them copy to a new location / loop through the entire column manually which I would like to avoid doing.
My requirement is to separate the rows from a single column in separate workbooks, based on the value of G2 column's cell (eg. All Houses, All Condos, All Apartments etc in separate files). I am attempting to use the AdvancedFilter in Excel VBA to extract each unique value into an array and then iterate through these unique values finding all the corresponding column values. I don't get how the AdvancedFilter works though.
xlFilterCopy along with copyToRange copies the list of unique values to the specified range.
How in the world does xlFilterValues work though? The reference says xlFilterValues "filters the data in place". But how do I GET these values? Does it return an object of some kind? In debug it just seems to give me a boolean value!
Why is this so oddly documented! Thanks for the help people.
PS: If you have a better way to do what I need without the advancedfilter please let me know.
With sheet
.Range(orgLevelLetter & "2", .Cells(.Rows.Count, orgLevelLetter).End(xlUp)).AdvancedFilter xlFilterInPlace, , , True
End With
Answer
You get the values because you specified the CopyToRange, therefore you know where the values went. So whatever you just used as a reference for that, you now use to manipulate it.
If you don't want to use the CopyToRange, then the values are returned by the call because AdvancedFilter returns a range object. Therefore you probably want to assign your .Range statement to a range that you have initialized already.
An alternative way of doing that would be to use the With statement this way:
With sheet.Range(orgLevelLetter & "2", _
.Cells(.Rows.Count,orgLevelLetter).End(xlUp)).AdvancedFilter(xlFilterInPlace, , , True)
<content here>
End With