Solved

Add an additional sort (Ascending)

Posted on 2011-09-12
6
301 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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,…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
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…

749 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