Unable to generate unique values using AdvancedFilter


try as I might, it seems impossible to make Excel FULLY produce a unique filter product. If I do this :

For q = 1 To livecolumns
    Sheet5.Columns(q).Copy Destination:=WS.Columns(1) 
    WS.Columns(1).AdvancedFilter Action:=xlFilterInPlace, Unique:=True


    WS.Columns(1).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet5.Columns(q)




Open in new window

this takes columns in the first sheet (Sheet5) and copies them one at a time to sheet WS. WS then applies the filter, and copies the visible cells back to the already-cleared column in Sheet5. WS is then cleared, ready for the next incoming column of data.

But if a set of values has two identical values contiguous - ie in one cell below the other - then the filter does not get rid of one of them - although it works on the rest of the column's values, and does produce a list of uniques. Nothing I have tried has been able to work around this. Any ideas greatly appreciated.

(PS If you are an expert wondering when I am going to close my other Excel Q, it is open because until I get this present problem solved, I cannot get to move the sheet up to Excel 2013.)
LVL 17
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Advanced Filter requires that the first row of data be header labels. If you try running the Advanced Filter manually, you'll get a warning message if Excel can't recognize the first row as being header labels.

One approach is to create two separate ranges, the second of which excludes the header row. After applying Advanced Filter to the first range, I copy the second range (i.e. visible cells excluding the header row) to a separate sheet.
Another approach is to use the Advanced Filter to put the results directly on the other sheet. I then create a range variable that excludes the first row of that results range (it always copies the header label), and use that to import the uniques into VBA or a Data Validation dropdown.

From the code snippet you posted, the first approach seems more appropriate. You don't need to use the SpecialCells(xlCellTypeVisible), however.

Sub FilterMe()
Dim WS As Worksheet
Dim rgData As Range, rgFiltered As Range
Dim q As Long

q = 1
Set WS = ActiveSheet
Set rgData = WS.Range("A1")
Set rgData = Range(rgData, WS.Cells(WS.Rows.Count, rgData.Column).End(xlUp))
Set rgFiltered = rgData.Offset(1, 0).Resize(rgData.Rows.Count - 1, 1)

rgData.AdvancedFilter Action:=xlFilterInPlace, Unique:=True

rgFiltered.Copy Destination:=Sheet5.Cells(1, q)


End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
krakatoaAuthor Commented:
Oueff! Wow thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.