Ask Experts Questions for FREE Help !
Ask
    kvinay_00's Avatar
    kvinay_00 Posts: 36, Reputation: 1
    Junior Member
     
    #1

    Jul 18, 2009, 04:05 AM
    Sorting in Drop down list
    I have large data running into many rows and new data is added regulary. As it is updated regulary, it is not possible to sort it every time.

    I am using a normal LIST validation for getting drop down list. The data shown in the drop down menu is not sorted out.

    I want that the data should be shown in a sorted manner in the drop down menu (irrespective of the type of data at the source).

    I am attaching a sample file. Please suggest.

    Thanks
    Attached Files
  1. File Type: xls test.xls (24.0 KB, 184 views)
  2. Perito's Avatar
    Perito Posts: 3,139, Reputation: 150
    Ultra Member
     
    #2

    Jul 18, 2009, 05:07 AM
    I added a VBA subroutine, shown below, to the "Workbook_SheetChange" event. Whenever you make a change to the workbook, this event will "fire. The way it is currently written, it will sort the first two columns (from rows 1 through 9999). In descending order. Of course, you can modify this to do as you wish. The drop-down box apparently sorts as column 1, but I didn't check that.

    You can make other programming changes in VBA. One of the simplest ways is to record a macro then get into VBA and copy the contents of the macro subroutine into the event of your choice. Sometimes you have to make changes to the macro (for example, the "Range", or Columns("A:B").Select might be Columns("A:D").Select, if you wanted to sort 4 columns) to make sure it will cover future situations.


    [CODE=VBA]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Columns("A:B").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("A2:A9999") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:B9999")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    End Sub[/CODE]
    Attached Files
  3. File Type: xls test.xls (38.5 KB, 203 views)
  4. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #3

    Jul 18, 2009, 09:40 AM
    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     Columns("A:B").Select
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A9999") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range("A1:B9999")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    
    End Sub
    Great suggestion. Just a tip... wrap your posted code in [code] tags instead [indent] tags and it's much easier to read. Go team.
    kvinay_00's Avatar
    kvinay_00 Posts: 36, Reputation: 1
    Junior Member
     
    #4

    Jul 20, 2009, 02:00 AM
    Thank you Perito.

    I tried to add some more data in the sheet and check. I am getting error. Can you look into please?

    I am attaching the sheet for your reference.

    Thanks
    Attached Files
  5. File Type: xls test 2.xls (41.0 KB, 174 views)
  6. kvinay_00's Avatar
    kvinay_00 Posts: 36, Reputation: 1
    Junior Member
     
    #5

    Jul 20, 2009, 08:33 PM
    Hello Perito,

    Any comments on this please?

    Thanks

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Sorting things out [ 1 Answers ]

How old were you when you met... was this the first girl? What caused you to feel she may not be the one and what about your new friend attracts you please answer and I will do my best to help

Combo box/list box/drop down box [ 1 Answers ]

I have a question for you in regards to a list box in a query. I have a form that I am using and want to give the users some options on what years they want to pull data from. So I was thinking about creating a list box or drop down box that would display the available years, when they highlighted...

Sorting and Sum in Excel [ 2 Answers ]

I'm trying to setup an excel spreadsheet to find the total dollar amount of comps given for several different vendors all out off one mass mixed up spreadsheet. I want to search for a specific name and then get a sum of the dollar amount in the cell to the right of it, over the entire list. Any...

Sorting data [ 2 Answers ]

hi there, another excel question. So I have 2 columns of interst in my spreadsheet. One column is a modulus of a running time score (all values range from 0-2000). The other column is a series of 0's, and some other numbers not of interst. I am interested in all of my modulus values that have 0...


View more questions Search