Advanced filter and transpose needs fix..

Posted on 2012-08-13
Last Modified: 2012-08-14
Advanced filter and transpose needs fix..

Hello All,

Two things:
The advanced filter is not correctly filtering the data as unique. There is a column containing repeating numbers of rows have 12 monthly dates that repeat. When the advanced filter is invoked to filter column A as unique data, it pulling in 2/1/2012 twice
Secondly, when the “Transpose” key is pressed multiple times, it keeps filling adjacent rows and columns very strangely. The expectation is every time the transpose is pressed, it only fills in the range “mySourceRange”, no other cells

So the ideal goal is to filter unique values from column A to column B – which will then have only 12 months - no repeating data here.  Please note: Column A can have upto 35 000 rows in actual,
Then sort that unique list and put in horizontally into range “mySourceRange”

Thank you
Question by:Rayne

    Author Comment


    Author Comment

    Or if it could be done more efficiently  - whichever way really..
    LVL 44

    Accepted Solution

    The cause of the problem is that your named range mySourceRange starts at A2.  The AdvancedFilter method assumed the first row contains the column title, so it is treating 2/1/2012 as a column header.

    I've modified your click event code to the following:
    Sub myFunc()
        Dim s As String
        Dim i As Long, n As Long
        Dim X As Variant
        Dim rng, mysortrange, cel As Range
        With Range([B2], [B65536].End(xlUp))
        End With
        'First get unique vlaues
        Range("mysourcerange").Worksheet.Range(Range("mysourcerange").Offset(-1, 0), Range("mysourcerange").Cells(1, 1).End(xlDown)).AdvancedFilter Action:=xlFilterCopy, copytorange:=Sheets(Sheet5.Name).Range("B2"), unique:=True
        Sheets(Sheet5.Name).Range("B2").Delete (xlShiftUp)   'Delete header value
        Call Data_Sort
        Set rng = Sheet5.Range("myRange")
        Set mysortrange = Range([B2], [B65536].End(xlUp))
        With Range([B2], [B65536].End(xlUp))
            X = .Value
            n = UBound(X)
            Set rng = ActiveSheet.Range("myRange")
            For i = LBound(X) To UBound(X)
                rng.Cells(i).Value = X(i, 1)
        End With
    End Sub

    Open in new window


    Author Comment

    perfect !! aikimark

    That works sweet

    Thank you :)

    Author Comment

    Hello Aikimark,

    Here is a follow up  question if you are interested

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    My experience with Windows 10 over a one year period and suggestions for smooth operation
    Outlook Free & Paid Tools
    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 how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    729 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

    21 Experts available now in Live!

    Get 1:1 Help Now