Solved

Add an additional sort (Ascending)

Posted on 2011-09-12
6
291 Views
Last Modified: 2012-05-12
Attached is some code that I use to filter a dataset.  After the filter is applied I would like to sort in ascending order oldest to newest, based on column T.

Is there a way to include this in the current filter statements?  

Please advise and thanks. -R-
With rgFilter
        .AutoFilter
        .AutoFilter Field:=7, Criteria1:="SPCLMDL"
                .AutoFilter Field:=10, Criteria1:=Array( _
        "CRTD", "PCNF LKD  REL", "PCNF REL", "REL"), Operator:=xlFilterValues
        .AutoFilter Field:=20, Criteria1:="<=" & Date, Operator:=xlAnd  'Late including today
        
    End With
End With

Open in new window

0
Comment
Question by:RWayneH
  • 4
  • 2
6 Comments
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36525949
Please try to add the following code after line 9 (above) and run.

    rgFilter.Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("T1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange rgFilter
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Open in new window


0
 

Author Comment

by:RWayneH
ID: 36526864
So do I place this after the current filter?  Is there a way to intergrate it into the current code? rather then having to place it after the current box of code?  It would be nice if I could keep it all together within the same with box. -R-
0
 
LVL 10

Accepted Solution

by:
SANTABABY earned 500 total points
ID: 36531160
Please add the following line within the "With rgFilter" code block

  .Sort Key1:=Range("T1"), Order1:=xlAscending, Header:=xlGuess, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:RWayneH
ID: 36531282
There is an issue with this solution.  My column headers are now on the bottom instead of the top...  How do I keep the headers at the top?  -R-
0
 

Author Comment

by:RWayneH
ID: 36531343
Nevermind, I test the one line code and that worked....  THANKS.  The one that threw my headers to the bottom was the first solution...

thank you both!! -R-
0
 

Author Closing Comment

by:RWayneH
ID: 36531353
EXCELent!!  Thank you so much.  -R-
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

860 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