RECrerar
09-04-2007, 03:31 AM
Hi, this is mainly a question of interest. I am populating a list box from a a column of data in a spreadsheet. There are several occurances of the same value in the column but I only want each different value to appear once in the listbox. For example the data may be:
test1
test1
test1
test2
test3
test3
etc....
and I would want the listbox to contain:
test1
test2
test3
The data is sorted and the code I am using (below) works fine
Private Sub UserForm_Initialize()
Dim row As Integer
row = 2
Do Until Cells(row, 1).Value = ""
If Cells(row, 1).Value <> Cells(row - 1, 1).Value Then
ListBox2.AddItem Cells(row, 1).Value
End If
row = row + 1
Loop
End Sub
The question is, is there a way to compare the values in the spreadsheet with the values already in the listbox rather than with the value in the spreadsheet so that if the data wasnt sorted I would still only get one occurence of each different value?
test1
test1
test1
test2
test3
test3
etc....
and I would want the listbox to contain:
test1
test2
test3
The data is sorted and the code I am using (below) works fine
Private Sub UserForm_Initialize()
Dim row As Integer
row = 2
Do Until Cells(row, 1).Value = ""
If Cells(row, 1).Value <> Cells(row - 1, 1).Value Then
ListBox2.AddItem Cells(row, 1).Value
End If
row = row + 1
Loop
End Sub
The question is, is there a way to compare the values in the spreadsheet with the values already in the listbox rather than with the value in the spreadsheet so that if the data wasnt sorted I would still only get one occurence of each different value?