Problem with creating a unique list for a combobox

CoachCarter · October 3rd, 2012, 12:47 AM

Hi guys,

I have made a contacts database, that needs a unique row source VBA code.

Purpose being so that I can make my comboboxes look like this

Carpender
Cleaner
Lawyer
Window Repair

rather than

Lawyer
Lawyer
Carpet Repair
Carpet Repair

Window Repair
Carpender
Carpender

etc.

i have found a code that seems to be effecient and i have changed the sheet names to reflect my workbook.

problem is it only accepts sheet1 as its destination range for the unique list and sheet2 as its source range. Now the code works like this ("If youre lucky as i have tested it in blank work books with mok-up data, but when i apply the code to my project and I very carefully make "All" my source range and "Sheet1" or whatever my destination, it tells me:

error 424 "Object required", simply because i changed the sheet name, and my sheet name is definetly written correcty!

If i use sheet2 which is in the original code, it sometimes comes up with error
"1004" Sort method of range class failed

Ok so here is the original code

Sub SortAndRemoveDupesOriginal()
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

Ok so i change only the sheet name in this part to All which is my contacts data list:

'Set range variable to list we want
Set rOldList = All.Range("A1", All.Range("A65536").End(xlUp))

and this gets the 424 error

If i use "sheet2" as the source sheet (which i really dont want to! as i have several other macro's using the "All" Sheet,

I still get the error "1004", sort method of range class failed.

If someone can please help me get over this problem I would be really gratefull and with this aid I could complete my contacts system which I was quite proud of up until this point.

If you need more info about what I am doing in the contacts system just let me know.

Sorry for alot of blab in explaining,

Thanks

mtranchi · October 3rd, 2012, 01:22 PM

why are u using

Code:

Set rOldList = All.Range("A1", All.Range("A65536").End(xlUp))

instead of

Code:

Set rOldList = Sheet2.Range("A1", Sheet2.Range("A65536").End(xlUp))

also, i've sometimes noticed that VBA is sometimes buggy, specifically with ranges in this case.

try skinning the cat in a different manner if need be like using

Code:

WorksheetFunction.CountA()

to find the last cell to delete

CoachCarter · October 11th, 2012, 09:09 PM

Sorry about this, but i should have mentioned that I have a very basic knowledge of VBA in excel. Although I have bought the VBA 24hr trainer book I still know very little at this stage about hard coding.

I tried the workbook.function.count(A) but again I got the 424 object required error.

Can sombody expand on this workbook.function.count method and show the full detail of how it would be applied in my code or if there is another method to avoid the object required error.

Thanks very much

mtranchi · October 13th, 2012, 10:16 PM

sorry, unfortunately i do not have a working copy of excel on this computer to make sure i give you sample code that works.

however, referring to my previous post, i noticed in the code that works, you use Sheet1. and in the code that doesn't work you use All.

So u might want to change All. to TheNameOfYourSheet. and see if that works.

As far as using WorkSheetFunction, like i said, i don't have a working copy of Excel at present so i can double check the following example works, and it's been some time since i've done VBA programming, but it would look something like this:

Code:

Sub Test()
  Dim iLastRowNumber As Integer
  
  'This should give u the total number of non-blank cells in the range
  iLastRowNumber = WorkSheetFunction.CountA(Sheet1.Range("A:A"))

  'So if u have say, two empty rows at the top of your spreadsheet, you would add these to the result to get the actual last row of data:
 iLastRowNumber = iLastRowNumber + 2

  'then you can use this number in a Cells function or however u want for sorting, etc.
End Sub

hope this gets you on the right track, sorry i can't be of more help