sort names based on alphabet doesn't work

leap out

Board Regular
Joined
Dec 4, 2020
Messages
110
Office Version
  1. 2016
  2. 2010
hi
I have this macro doesn't work in cells a1 is dropdown I would sort based alphabet for instance if the list contains asd1,asd2,asd3 bbd,bdd1,bbb2 ,cdd,ccd,ccc
so should the list like this
asd1
asd2
asd3
bbb2
bbd1
bdd1
ccc
ccd
cdd
another thing I would when write in cells a1 manually for instance asd1 then show list
asd1
asd2
asd3
VBA Code:
Sub Sort_B()
    With Sheets("sort").UsedRange
        .Sort Key1:=.Range("a1"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    End With
End Sub
note: if is possible do the macro without button automatically
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

leap out

Board Regular
Joined
Dec 4, 2020
Messages
110
Office Version
  1. 2016
  2. 2010
@Akuini your file is very great , may you adjust making the combobox doesn't show any item is repeated ,please?;)
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
may you adjust making the combobox doesn't show any item is repeated ,please?
Try this one:
The code;
VBA Code:
Option Explicit
'=============== YOU MAY NEED TO ADJUST THE CODE IN THIS PART: ===================================

'sheet's name where the list (for combobox) is located. [in the sample: sheet "deList"]
Private Const sList As String = "deList"

'cell where the list start [in the sample: cell A2 in sheet "deList" ]
Private Const sCell As String = "A2"

'the linked cell (cell that link to the combobox)
Private Const xCell As String = "B3"

Private d As Object
Private c As Range

Private Sub ComboBox1_Change()
Dim z, ary

With ComboBox1
    If .Value <> "" And IsError(Application.Match(.Value, d.keys, 0)) Then
            ary = Application.Transpose(Application.Transpose(d.keys))
                For Each z In Split(.Value, " ")
                    ary = Filter(ary, z, True, vbTextCompare)
                Next
           
           .List = ary
           .DropDown
    
    ElseIf Not IsError(Application.Match(.Value, d.keys, 0)) Then
        Range(xCell) = .Value
    
    Else
        Range(xCell) = .Value
        .List = d.keys
    End If
End With
End Sub


Private Sub ComboBox1_GotFocus()
Dim va, x
With Sheets(sList)
    Set c = .Range(sCell, .Cells(.Rows.Count, .Range(sCell).Column).End(xlUp))
    c.Sort Key1:=.Range(sCell), Order1:=xlAscending, Header:=xlNo 'sort the range to sort the list
    va = c

    Set d = CreateObject("scripting.dictionary")
    d.CompareMode = vbTextCompare
    
    'create unique list
    For Each x In va
        d(x) = Empty
    Next
    
End With

    ComboBox1.MatchEntry = fmMatchEntryNone
    ComboBox1.Value = ""
    'ComboBox1.ListRows = 10 'to show how many item
End Sub
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome, glad to help, & thanks for the feedback.:)
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
as a Reference and lesson learn this is because of version level


I tested it with 2013 and found
.Sort.SortFields.Add2 Key:=KyRng
to be
.Sort.SortFields.Add Key:=KyRng


Sub sort_names_based_on_alphabet.gif
 

leap out

Board Regular
Joined
Dec 4, 2020
Messages
110
Office Version
  1. 2016
  2. 2010

ADVERTISEMENT

@Dossfm0q thanks for your effort ,now there is no error , but the dropdown in column b is empty there is no items
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
 

Watch MrExcel Video

Forum statistics

Threads
1,127,872
Messages
5,627,393
Members
416,245
Latest member
Xterminat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top