I'm new to VBA and have been using a piece of code to sort, remove duplicates and populate a Combobox from a certain range on my worksheet. My question is, what additions do I need to make so that I can populate another Combobox from a different Column and still have it sort.
Code I'm using is as below. As you can see I'm currently filling cboTask with information starting from B4. I want to add another range to fill another Combobox, which would be cboEquipment with information starting at D4.
Dim Cell As Range
Dim Col As Variant
Dim Descending As Boolean
Dim Entries As Collection
Dim Items As Variant
Dim index As Long
Dim j As Long
Dim RngBeg As Range
Dim RngEnd As Range
Dim row As Long
Dim Sorted As Boolean
Dim temp As Variant
Dim test As Variant
Dim Wks As Worksheet
Set Wks = ThisWorkbook.Worksheets("Maintenance")
Set RngBeg = Wks.Range("b4")
Col = RngBeg.Column
Set RngEnd = Wks.Cells(Rows.Count, Col).End(xlUp)
Set Entries = New Collection
ReDim Items(0)
For row = RngBeg.row To RngEnd.row
Set Cell = Wks.Cells(row, Col)
On Error Resume Next
test = Entries(Cell.Text)
If Err = 5 Then
Entries.Add index, Cell.Text
Items(index) = Cell.Text
index = index + 1
ReDim Preserve Items(index)
End If
On Error GoTo 0
Next row
index = index - 1
Descending = False
ReDim Preserve Items(index)
Do
Sorted = True
For j = 0 To index - 1
If Descending Xor StrComp(Items(j), Items(j + 1), vbTextCompare) = 1 Then
temp = Items(j + 1)
Items(j + 1) = Items(j)
Items(j) = temp
Sorted = False
End If
Next j
index = index - 1
Loop Until Sorted Or index < 1
cboTask.List = Items
Thank you in advance, I thought this would be as simple as copying the code and changing the dim values, but it doesn't seem to work.