What is the problem you are having?
Also, did you name your combo box txtWeight?
[ This Message was edited by: Russell Hauf on 2002-03-26 15:12 ]
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
What is the problem you are having?
Also, did you name your combo box txtWeight?
[ This Message was edited by: Russell Hauf on 2002-03-26 15:12 ]
Yes, I did name my combobox as txtWeight
I got an error on the following line
Sort Key1:=.Cells(2, 2), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
I can't figure out what cause this problem
Please help
Thanks in advance
Hi Allen
Did you download the example that goes with this?
You have made modifications to the code, but not allowed for this in other parts of the code! For example you have:
'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
I doubt this would work as you are trying to tell Advanced filter to Copy uniques to another range, but 'the range' is the range of the original list!
_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training
[ This Message was edited by: Dave Hawley on 2002-03-26 18:17 ]
Hello Dave or Anyone
Is the line below
'Set range variable to list we want
Set rOldList = Sheet2.Range("A1", Sheet2.Range("A65536").End(xlUp))
the same as the line below?
Set rOldList = Columns(1)
Thanks in advance
Hello
Can anyone tell me howcome the line below works
'Clear Hidden sheet Column A ready for list
Sheet1.Range("A1", Sheet1.Range("A65536").End(xlUp)).Clear
and the line below doesn't work. Aren't they the same???
Worksheets("Hidden").Range("A1", Sheet1.Range("A65536").End(xlUp)).Clear
Thanks
Allen,
Will you go through my example file..
16. Populating Combobox
here it has shown how to populate the comboboxes secondly to your code which removes duplicate..
Okay..
will you try my code and make necessay modifications.
you can get code for deleting duplicates or say extracting unique values from my website...
file nos is 5 ( it is a simple example) and other file which can interest you is file nos
22.
I hope if you study this two file will solve your problem
if you still have difficulty then write back to me
http://www.pexcel.com/download.htm
ni****h desai
http://www.pexcel.com
Hello nisht
can you give me the VBA code passward for the file #5,16,22
Thanks
Allen,
please write me on
nisht@pexcel.com
i will sent you password.
and it is free
ni****h desai
Hello Nisht
can you send me the password now
sorry I don't mean to rush you. I am just trying to finish this application before my boss come this afternoon
Thanks in advance
Like this thread? Share it with others