Combobox

[AllenL]

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

 

[Russell Hauf]

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

 

[AllenL]

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

 

[Dave Hawley]

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

 

[AllenL]

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

 

[AllenL]

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

 

[nisht]

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

 

[AllenL]

Hello nisht
can you give me the VBA code passward for the file #5,16,22
Thanks

 

[nisht]

Allen,
please write me on

nisht@pexcel.com

i will sent you password.

and it is free

ni****h desai

 

[AllenL]

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