auto ascending order of list item

[rkpatel]

i create a list name is "Item Name" in sheet 1 A1:A100. i need when every time i add item in list the list is arrange in auto ascending order how to.

 

[kchiba]

I think that this can only be done with VBA

kanti

 

[sgmpatnaik]

@rkpatel

Please try the below code in Worksheet Module

Private Sub Worksheet_Change(ByVal Target As Range)
  With ActiveWorkbook.Worksheets("Sheet1").Sort
  .SetRange Range("A:A")
  .Header = xlGuess
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  Range("A65536").End(xlUp).Select
End Sub

if you have header in A1 as ITEM_LIST then try this code

Private Sub Worksheet_Change(ByVal Target As Range)
  With ActiveWorkbook.Worksheets("Sheet1").Sort
  .SetRange Range("A:A")
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  Range("A65536").End(xlUp).Select
End Sub

Hope it solve your problem

Thanks

 

[rkpatel]

@rkpatel

Please try the below code in Worksheet Module

Private Sub Worksheet_Change(ByVal Target As Range)
  With ActiveWorkbook.Worksheets("Sheet1").Sort
  .SetRange Range("A:A")
  .Header = xlGuess
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  Range("A65536").End(xlUp).Select
End Sub

if you have header in A1 as ITEM_LIST then try this code

Private Sub Worksheet_Change(ByVal Target As Range)
  With ActiveWorkbook.Worksheets("Sheet1").Sort
  .SetRange Range("A:A")
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  Range("A65536").End(xlUp).Select
End Sub

Hope it solve your problem

Thanks

this is not working

 

[Somendra Misra]

@rkpatel
Use below code. Insert in the sheet. Do not create a separate module for it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range("a:a")) Is Nothing Then


    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A:A")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
  End If

End Sub

Regards!

 

[sgmpatnaik]

@rkpatel

Please Try the Below Code

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A2000"), _
  SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  With ActiveWorkbook.Worksheets("Sheet1").Sort
  .SetRange Range("A1:A2000")
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  Range("A65536").End(xlUp).Select
End Sub

for your reference i attach a sample file please check

Hope it clear now

Thanks