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

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 621
  • Last Modified:

Advanced filter and transpose needs fix..

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
  • 4
1 Solution
RayneAuthor Commented:
RayneAuthor Commented:
Or if it could be done more efficiently  - whichever way really..
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

RayneAuthor Commented:
perfect !! aikimark

That works sweet

Thank you :)
RayneAuthor Commented:
Hello Aikimark,

Here is a follow up  question if you are interested

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now