Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Sorting in Drop down list (https://www.askmehelpdesk.com/showthread.php?t=376958)

  • Jul 18, 2009, 04:05 AM
    kvinay_00
    1 Attachment(s)
    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
  • Jul 18, 2009, 05:07 AM
    Perito
    1 Attachment(s)
    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]
  • Jul 18, 2009, 09:40 AM
    JBeaucaire
    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.
  • Jul 20, 2009, 02:00 AM
    kvinay_00
    1 Attachment(s)
    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
  • Jul 20, 2009, 08:33 PM
    kvinay_00
    Hello Perito,

    Any comments on this please?

    Thanks

  • All times are GMT -7. The time now is 12:24 AM.