[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Unable to generate unique values using AdvancedFilter

Posted on 2014-08-24
Medium Priority
Last Modified: 2014-08-24

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.)
Question by:krakatoa
LVL 81

Accepted Solution

byundt earned 2000 total points
ID: 40281716
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

LVL 17

Author Closing Comment

ID: 40281774
Oueff! Wow thanks!

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question