Hello
I am trying to create a combobox that has a list of data in a column that elimate the duplicated data and sort them.
I have a code(from the website) that creates very similar function I need. The only differences is that it uses listbox and not combobox. Then I try to modify it to combobox but I can't get it to work
The following is the code for the web:
Sub SortAndRemoveDupes()
Dim rListSort As Range, rOldList As Range
Dim strRowSource As String
'Clear Hidden sheet Column A ready for list
Sheet1.Range("A1", Sheet1.Range("A65536").End(xlUp)).Clear
'Set range variable to list we want
Set rOldList = Sheet2.Range("A1", Sheet2.Range("A65536").End(xlUp))
'Use AdvancedFilter to copy the list to Column A _
of the hidden sheet and remove all dupes
rOldList.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheet1.Cells(1, 1), Unique:=True
'Set range variable to the new non dupe list
Set rListSort = Sheet1.Range("A1", Sheet1.Range("A65536").End(xlUp))
With rListSort
'Sort the new non dupe list
.Sort Key1:=.Cells(2, 1), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
'Parse the address of the sorted unique items
strRowSource = Sheet1.Name & "!" & Sheet1.Range _
("A2", Sheet1.Range("A65536").End(xlUp)).Address
Sheet1.Range("A1") = "New Sorted Unique List"
With UserForm1.ListBox1
'Clear old ListBox RowSource
.RowSource = vbNullString
'Parse new one
.RowSource = strRowSource
End With
End Sub
This is the code after I modified:
Sub SortAndRemoveDupes()
Dim rListSort As Range, rOldList As Range
Dim strRowSource As String
'Clear Hidden sheet Column A ready for list
'Set range variable to list we want
Set rOldList = Columns(2)
'Use AdvancedFilter to copy the list to Column A _
of the hidden sheet and remove all dupes
rOldList.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Cells(1, 2), Unique:=True
'Set range variable to the new non dupe list
Set rListSort = Columns(2)
With rListSort
'Sort the new non dupe list
.Sort Key1:=.Cells(2, 2), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
'Parse the address of the sorted unique items
strRowSource = Columns(2).Address
MsgBox "StrRowSource" & strRowSource
With UserForm1.txtWeight
'Clear old ListBox RowSource
.RowSource = vbNullString
'Parse new one
.RowSource = strRowSource
'.AddItem = strRowSource
End With
End Sub
Can someone please help
Thanks in advance
I am trying to create a combobox that has a list of data in a column that elimate the duplicated data and sort them.
I have a code(from the website) that creates very similar function I need. The only differences is that it uses listbox and not combobox. Then I try to modify it to combobox but I can't get it to work
The following is the code for the web:
Sub SortAndRemoveDupes()
Dim rListSort As Range, rOldList As Range
Dim strRowSource As String
'Clear Hidden sheet Column A ready for list
Sheet1.Range("A1", Sheet1.Range("A65536").End(xlUp)).Clear
'Set range variable to list we want
Set rOldList = Sheet2.Range("A1", Sheet2.Range("A65536").End(xlUp))
'Use AdvancedFilter to copy the list to Column A _
of the hidden sheet and remove all dupes
rOldList.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheet1.Cells(1, 1), Unique:=True
'Set range variable to the new non dupe list
Set rListSort = Sheet1.Range("A1", Sheet1.Range("A65536").End(xlUp))
With rListSort
'Sort the new non dupe list
.Sort Key1:=.Cells(2, 1), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
'Parse the address of the sorted unique items
strRowSource = Sheet1.Name & "!" & Sheet1.Range _
("A2", Sheet1.Range("A65536").End(xlUp)).Address
Sheet1.Range("A1") = "New Sorted Unique List"
With UserForm1.ListBox1
'Clear old ListBox RowSource
.RowSource = vbNullString
'Parse new one
.RowSource = strRowSource
End With
End Sub
This is the code after I modified:
Sub SortAndRemoveDupes()
Dim rListSort As Range, rOldList As Range
Dim strRowSource As String
'Clear Hidden sheet Column A ready for list
'Set range variable to list we want
Set rOldList = Columns(2)
'Use AdvancedFilter to copy the list to Column A _
of the hidden sheet and remove all dupes
rOldList.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Cells(1, 2), Unique:=True
'Set range variable to the new non dupe list
Set rListSort = Columns(2)
With rListSort
'Sort the new non dupe list
.Sort Key1:=.Cells(2, 2), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
'Parse the address of the sorted unique items
strRowSource = Columns(2).Address
MsgBox "StrRowSource" & strRowSource
With UserForm1.txtWeight
'Clear old ListBox RowSource
.RowSource = vbNullString
'Parse new one
.RowSource = strRowSource
'.AddItem = strRowSource
End With
End Sub
Can someone please help
Thanks in advance