Solved

Custom List Sort for Large Spreadsheet

Posted on 2011-03-11
2
425 Views
Last Modified: 2012-05-11
I have a large spreadsheet containing stock data.  The file has 96 columns and becomes very time consuming when changing sorts from one column to another.  For example...of the 96 columns, I may want to sort ascending/descending values for 20 columns (with other columns containing supporting data).  Instead of scrolling to each column, I would prefer to create a dropdown list that will allow me to select a column header and sort it accordingly.  I have attached a trimmed down version that hopefully makes it easier to understand.  The custom list is in Cell A2.  Thanks in advance to anyone who can help out. SortList.xls
0
Comment
Question by:maverickcapital
[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
2 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35110457
Sample Attached. Hope it helps :)

Sid

Code Used

Dim i As Long, lastRow As Long
Dim aCell As Range, sortRange As Range
Dim strSearch As String
    
Sub Ascd()
    strSearch = ActiveSheet.Range("A2").Value
    
    If Len(Trim(strSearch)) = 0 Then
        MsgBox "Please select the header on which you want to sort"
    End If
    
    lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    
    Set sortRange = ActiveSheet.Range("A5:T" & lastRow)
    
    Set aCell = ActiveSheet.Rows(4).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        sortRange.Sort Key1:=ActiveSheet.Range(Split(Cells(, aCell.Column).Address, "$")(1) & "5"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Else
        MsgBox "Not Found"
    End If
End Sub

Sub Dscd()
    strSearch = ActiveSheet.Range("A2").Value
    
    If Len(Trim(strSearch)) = 0 Then
        MsgBox "Please select the header on which you want to sort"
    End If
    
    lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    
    Set sortRange = ActiveSheet.Range("A5:T" & lastRow)
    
    Set aCell = ActiveSheet.Rows(4).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        sortRange.Sort Key1:=ActiveSheet.Range(Split(Cells(, aCell.Column).Address, "$")(1) & "5"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Else
        MsgBox "Not Found"
    End If
End Sub

Open in new window

SortList.xls
0
 

Author Closing Comment

by:maverickcapital
ID: 35111689
you the man.  Works perfectly.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

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