BrianExcel
Well-known Member
- Joined
- Apr 21, 2010
- Messages
- 975
I am using teh following code to identify a range, filter out all duplicate values and then populate a ComboBox based on that range.
For some reason, with this code, it copies the first value twice. When I am done, the list successfully loads with the following values, in order:
Corporate Use
Corporate Use
Marketing
PM IC
I dont know why it's copying that first "corporate use" twice. There is more than one occurrence of that value, but I have checked and all are identical (no extra spaces, incorrect spellings, etc.).
Thoughts?
Code:
Sub Userform_Initialize()Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnData As Range
'Variant to contain the data to be placed in the combo box.
Dim vaData As Variant
'Initialize the Excel objects
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Expense Account Guide")
'Set the range equal to the data, and then (temporarily) copy the unique values of that data to the L column.
With wsSheet
Set rnData = .Range(.Range("A1"), .Range("A500").End(xlUp))
rnData.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("L1"), Unique:=True
'store the unique values in vaData
vaData = .Range(.Range("L1"), .Range("L500").End(xlUp)).Value
'clean up the contents of the temporary data storage
Range(.Range("L1"), .Range("L50").End(xlUp)).ClearContents
End With
'display the unique values in vaData in the combo box already in existence on the worksheet.
With frmExpenseAccountGuide.cmbAccountType
.Clear
.List = vaData
End With
End Sub
For some reason, with this code, it copies the first value twice. When I am done, the list successfully loads with the following values, in order:
Corporate Use
Corporate Use
Marketing
PM IC
I dont know why it's copying that first "corporate use" twice. There is more than one occurrence of that value, but I have checked and all are identical (no extra spaces, incorrect spellings, etc.).
Thoughts?