Unable to generate unique values using AdvancedFilter

Posted on 2014-08-24
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 80

    Accepted Solution

    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 16

    Author Closing Comment

    Oueff! Wow thanks!

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now