Sort list of ComboBox Alphabetically in Excel

 Sorting data Alphabetically in ComboBox || Remove duplicate from ComboBox || AddItem in ComboBox with multiple Column

Today In this tutorial we would learn How to sort data in ComboBox in Excel VBA. Key Highlihgts 1. Data Add to ComboBox from Worksheet 2. Removing Duplicate Items from ComboBox 3. Sorting ComboBox List Alphabetically with out changing worksheet data by VBA excel To sort the list in ComboBox.. We need to take two variables a and b. Now set the variable a to count the list from 1st item to last item and the variables b to count a items to last item of the combobox. Now need a variant c ( VBA Variant is a VBA variable that has not been declared to have a specific data type. It can therefore hold any type of data that VBA variables are able to hold – be it text, numbers, dates, time or objects.) Now apply condition if list(a) is greater than list(b) then ( that means if list(a) is 1st item then list(b) is second item of combobox ) variant c is equal to list(a) Now to compare the list item which is nearby equal to 1st position alphabetically or as value so list(a) = List(b) now change or rearrange or sort the list as list(b) = c now you can run it. For more videos and information, Please visit https://nsutradhar.blogspot.com/ If you like my video tutorial pls Subscribe my Channel

VBA CODES

Option Explicit

Private Sub ComboBox1_Change()

ComboBox1.DropDown ' it will help to auto dropdown while typing

End Sub

Private Sub UserForm_Initialize()

Dim a, b, r As Long, c As Variant

For r = 2 To Sheet1.Range("C1000").End(xlUp).Row

If Application.WorksheetFunction.CountIf(Sheet1.Range("C2", "C" & r), Sheet1.Cells(r, 3)) = 1 Then

With Me.ComboBox1

.AddItem Sheet1.Cells(r, 3)

.List(.ListCount - 1, 1) = Sheet1.Cells(r, 3).Offset(0, 1).Value

End With

End If

Next r

With Me.ComboBox1

    For a = 0 To .ListCount - 1

    For b = a To .ListCount - 1

            If .List(a) > .List(b) Then

        c = .List(a)

        .List(a) = .List(b)

        .List(b) = c

        End If

       Next

      Next

      End With

End Sub

Click here to download this file

If you want to show the 2nd column data as per database, please use the below code and just change sorting vba codes

Dim x as integer

With Me.ComboBox1 For a = 0 To .ListCount - 1 For b = a To .ListCount - 1 If .List(a) > .List(b) Then For x = 0 To 1 c = .List(a, x) .List(a, x) = .List(b, x) .List(b, x) = c Next x End If Next b Next a End With

click here to download 2nd file

For Support and Feedback

Plesae Subscribe me

Download File