Transfer AutoFiler list to a ComboBox
Hi Lemi,
Do I interpret your request correctly in that you want ot create a unique
list of data from a current list that is not unique. You then want to sort
the unique list and use it for the combo box row source? If so then try the
following.
I have included a number of comments to help you to understand what the code
is doing but feel free to get back to me with any questions.
Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.
Sub CreateUniqueList()
'Define name for original data
'Assumes original data in column A
'starting with column header in cell A1.
With Sheets("Sheet1")
.Range(.Cells(1, "A"), _
.Cells(.Rows.Count, "A") _
.End(xlUp)).Name = "MyData"
End With
'Use another worksheet for unique data
With Sheets("Sheet2")
'Advanced Filter to extract
'unique records
Range("MyData").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("A1"), _
Unique:=True
'Define name for unique data
'including column header.
.Range(.Cells(1, "A"), _
.Cells(.Rows.Count, "A") _
.End(xlUp)).Name = "UniqHeadAndDat"
'Define a name for the data ony
'excluding the column header.
.Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A") _
.End(xlUp)).Name = "UniqueDatOnly"
'Clear any existing sort fields.
.Sort.SortFields.Clear
'Set the sort parameters.
.Sort.SortFields _
.Add Key:=.Range("UniqueDatOnly"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
'Sort the data.
With .Sort
.SetRange Range("UniqHeadAndDat")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
UserForm1.ComboBox1.rowsource = "UniqueDatOnly"
End Sub
--
Regards,
OssieMac
|