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

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.




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

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.

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

Hello Perito,

Any comments on this please?

Thanks