Selected Answer
Leopard
Please try the attached file (full code below) which will populate the Code, Brand and Model comboboxes without duplicates. Note that I've corrected your sheet and combobox to use the correct spelling, Warehouse.
I've set up three arrays at the start e.g. CodeArr() . These get populated (and resized) with every matching item found in each of the sheets (using separate counters) as in this example (with extra comments added):
For Each rCell In .Range("b2", .Cells(Rows.Count, 2).End(xlUp))
i = i + 1 'increment a counter (and keep for next sheet)
ReDim Preserve CodeArr(i) 'make the array bigger, preserving values (leaving entry 0 as blank)
CodeArr(i) = rCell.Value 'make the ith value of the array the cell value; you should collecting other array data here too
Next rCell 'Loop within the sheet
When the For Next (Ws) loop is done, they're will be 3 arrays, containing vlaues from all three sheets.
I then call a function ArrayRemoveDups (from another forum VBA – Remove Duplicates From Array) to remove the duplicates and populate the combox box with the (unduplicated) values from the clean array "output" e.g.:
Output = ArrayRemoveDups(CodeArr) ' call function to remove duplicates
For i = 0 To UBound(Output) 'populate combobox list
ComboBox1.AddItem Output(i)
Next i
.
Put it all together you get this code (and you'll see the ArrayRemoveDups function code in the file):
Private Sub UserForm_Initialize()
Dim Ws As Worksheet, rCell As Range, rcell1 As Range, rcell2 As Range
Dim CodeArr() As Variant, BrandArr() As Variant, ModelArr() As Variant
For Each Ws In ActiveWorkbook.Worksheets
With Ws
For Each rCell In .Range("b2", .Cells(Rows.Count, 2).End(xlUp))
i = i + 1
ReDim Preserve CodeArr(i)
CodeArr(i) = rCell.Value 'collect other array data here?
Next rCell
For Each rcell1 In .Range("c2", .Cells(Rows.Count, 3).End(xlUp))
m = m + 1
ReDim Preserve BrandArr(i)
BrandArr(m) = rcell1.Value 'collect other array data here?
Next rcell1
For Each rcell2 In .Range("d2", .Cells(Rows.Count, 4).End(xlUp))
n = n + 1
ReDim Preserve ModelArr(i)
ModelArr(n) = rcell2.Value 'collect other array data here?
Next rcell2
End With
Next Ws
Output = ArrayRemoveDups(CodeArr) ' call function to remove duplicates
For i = 0 To UBound(Output) 'populate combobox list
ComboBox1.AddItem Output(i)
Next i
Output = ArrayRemoveDups(BrandArr)
For i = 0 To UBound(Output)
ComboBox2.AddItem Output(i)
Next i
Output = ArrayRemoveDups(ModelArr)
For i = 0 To UBound(Output)
ComboBox3.AddItem Output(i)
Next i
End Sub
Incidentally your original macro has an unnecessary Select Case statement (could be removed without issue) and incorrectly nested loops (which caused many more duplicates than necessary). Also note that if you DIM rcell (without "As Range"), it will be the default type (Variant); only rcell2 is correctly scoped As Range in your DIM statement.
Hope the code above helps- I leave you to think how you collect/present the other data.