Pull Down List using data from multi sources

Snowshoeken

Active Member
Joined
Aug 8, 2002
Messages
306
I need to make a pulldown list of what is essentially 11 digit codes that are listed on different tabs. There are some duplicates. In the end I want to place this pull down list in a User Form. Any ideas? Thanks.

Snowshoeken
"Realizing you have nothing, allows you to do anything."
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Names of the sheets are

AL Zones, FL Zones and GA Zones. The data is located in the first 5 colums with varying numbers of rows. Thanks

Snoshoeken
 
Upvote 0
This will fill ComboBox1 with unique items:

Code:
Private Sub UserForm_Initialize()
    Dim Codes As New Collection
    Dim Rng As Range
    Dim c As Range
    Dim i As Long
    Dim j As Long
    Dim Temp As Variant
    With Worksheets("AL Zones")
        Set Rng = .Range("A1:A" & .Range("A65536").End(xlUp).Row)
        On Error Resume Next
        For Each c In Rng
            Codes.Add c.Value, CStr(c.Value)
        Next c
        On Error GoTo 0
    End With
    With Worksheets("FL Zones")
        Set Rng = .Range("A1:A" & .Range("A65536").End(xlUp).Row)
        On Error Resume Next
        For Each c In Rng
            Codes.Add c.Value, CStr(c.Value)
        Next c
        On Error GoTo 0
    End With
    With Worksheets("GA Zones")
        Set Rng = .Range("A1:A" & .Range("A65536").End(xlUp).Row)
        On Error Resume Next
        For Each c In Rng
            Codes.Add c.Value, CStr(c.Value)
        Next c
        On Error GoTo 0
    End With
    If Codes.Count > 1 Then
        For i = 1 To Codes.Count - 1
            For j = i + 1 To Codes.Count
                If Codes(i) > Codes(j) Then
                    Temp = Codes(j)
                    Codes.Remove (j)
                    Codes.Add Temp, CStr(Temp), i
                End If
            Next j
        Next i
    End If
    For i = 1 To Codes.Count
        ComboBox1.AddItem Codes(i)
    Next i
End Sub
 
Upvote 0
So far it is working really well. Was easy to add additional sheets. Thanks you.

Was wondering, would this work in a User Form?

Thanks again.

Snowshoeken.
 
Upvote 0
Another Q. The header for the 11 digit codes isn't "codes". It is actually "Switches", should I change all the ref. to "codes" to the colmn header? Thanks again.

Snowshoeken
 
Upvote 0
No, Codes is just a name I gave the Collection. If you want to exclude the header row from the list, change:

Code:
Set Rng = .Range("A1:A" & .Range("A65536").End(xlUp).Row)

to

Code:
Set Rng = .Range("A2:A" & .Range("A65536").End(xlUp).Row)
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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
Back
Top