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

x
  • 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
transposemm.xlsm
0
Rayne
Asked:
Rayne
  • 4
1 Solution
 
RayneAuthor Commented:
0
 
RayneAuthor Commented:
Or if it could be done more efficiently  - whichever way really..
0
 
aikimarkCommented:
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))
          .ClearContents
    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")
    
    rng.ClearContents
     
    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)
            
        Next
    
    End With

End Sub

Open in new window

0
 
RayneAuthor Commented:
perfect !! aikimark

That works sweet

Thank you :)
0
 
RayneAuthor Commented:
Hello Aikimark,

Here is a follow up  question if you are interested
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_27828838.html
0

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