I got this from a book and it is almost what I need but I need to twea
it and can't seem to make it work.

The For i& makes me pick a range from 3 to 300 or a different number
Is there a way to make it pickup only unique names in that column, s
that the combo box only populates with those names and not a bunch o
blank spaces???

Private Sub UserForm_Initialize()
Dim i&
With ThisWorkbook.Sheets("Active Collection")
For i& = 3 To 300
cmbClient.AddItem .Cells(i&, 4).Value
Next i&
End With
cmbClient.ListIndex = 0
End Sub

I would like to still start with row 3

--
Message posted from http://www.ExcelForum.com

You need to get a unique range before loading the combobox.

Take a look at DataFilterAdvanced Filter with the copy to another
location, un ique records.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"stck2mlon " wrote in message
...
I got this from a book and it is almost what I need but I need to tweak
it and can't seem to make it work.

The For i& makes me pick a range from 3 to 300 or a different number.
Is there a way to make it pickup only unique names in that column, so
that the combo box only populates with those names and not a bunch of
blank spaces???

Private Sub UserForm_Initialize()
Dim i&
With ThisWorkbook.Sheets("Active Collection")
For i& = 3 To 300
cmbClient.AddItem .Cells(i&, 4).Value
Next i&
End With
cmbClient.ListIndex = 0
End Sub

I would like to still start with row 3.


---
Message posted from http://www.ExcelForum.com/

stck2mlon,
Here is an answer given to a similar post by Carl
I am posting it as it is. with adjustments to suit your environment you can
get it work.
It worked for me.
HTH,
Cecil

He
subSetupform()
For Each c In frmAddEntry.Controls
If Left(c.Name, 2) = "cb" Then
c.List = CreateList(YourRangeAddress)
End If
Next c
End Sub


Function CreateList(myRange)
Dim myControl
Dim mystring As String
Dim Cell As Range
Dim NoDupes As New Collection
Dim i As Integer
Dim j As Integer
Dim Swap1, Swap2, Item
Dim cbList() As Variant

' The next statement ignores the error caused by attempting to add
' a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In Range(myRange)
If Cell.Value < "" Then NoDupes.Add Cell.Value, Cell.Value
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell

On Error GoTo 0

' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, befo=j
NoDupes.Add Swap2, befo=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' Add the sorted, non-duplicated items to a ComboBox
ReDim cbList(NoDupes.Count) 'reset to same number as in no dupes
j = 0
For Each Item In NoDupes
j = j + 1
If Item < "" Then cbList(j) = Item
Next Item

For j = 1 To NoDupes.Count ' Remove names from the collection.
NoDupes.Remove 1 ' removes the current first member on each
iteration.
Next

CreateList = cbList()
End Function

Now go buy a Power Programming book by :John Walkenbach
BEST investment you'll ever make!

Carl


"bcorbin " wrote in message
...
:(
Sorry if this is a stupid problem but I am very new to VBA. I am
writing a VBA app in a CAD program and I have managed to get a
multi-column listbox which is exactly what I want, except for the
duplicate entries (see attached image).
Is there any way to now get rid of the duplicates (entire row) from the
listbox?

Thanks for any help anyone can provide, as I'm really lost.... ;-)

Brian

Attachment filename: listbox.jpg
Download attachment:
http://www.excelforum.com/attachment.php?postid=550502
---
Message posted from http://www.ExcelForum.com/


"stck2mlon " wrote in message
...
I got this from a book and it is almost what I need but I need to tweak
it and can't seem to make it work.

The For i& makes me pick a range from 3 to 300 or a different number.
Is there a way to make it pickup only unique names in that column, so
that the combo box only populates with those names and not a bunch of
blank spaces???

Private Sub UserForm_Initialize()
Dim i&
With ThisWorkbook.Sheets("Active Collection")
For i& = 3 To 300
cmbClient.AddItem .Cells(i&, 4).Value
Next i&
End With
cmbClient.ListIndex = 0
End Sub

I would like to still start with row 3.


---
Message posted from http://www.ExcelForum.com/

Try this-

Private Sub UserForm_Initialize()
Dim i&, x&, Unique As Boolean
With Sheets("Active Collection")
For i = 3 To 300
If Cells(i, 4).Value < "" And i 3 Then
Unique = True
For x = 4 To i
If .Cells(x - 1, 4) = .Cells(i, 4).Value Then
Unique = False
Exit For
End If
Next x
If Unique Then
cmbClient.AddItem .Cells(i, 4).Value
End If
End If
Next i
End With
cmbClient.ListIndex = 0
End Sub

Regards,
billyb


---
Message posted from http://www.ExcelForum.com/

Thanks everyone...these both worked great

--
Message posted from http://www.ExcelForum.com