Is it possible to limit a listbox based on a range to only retrieve distinct values?

Hi,

I have a listbox on a form that pulls data from several sheets. The list box pulls from a spreadsheet range called History. Right now it pulls all rows of the same item but has different dates and status of the item. I want it to only pull the distinct item number and lot number from the range. I thought I could do this with sql statement but since the actual table is an Excel range in the same workbook I don't necessarily know how to call the data source.

Right now the property of the listbox is set to rowsource = History

I basically want to filter that by using

Select Distinct(History.Kit), History.Lot from History

Any help would be appreciated.

Gvsandrs

Ok I've made some headway. I managed to get the listbox to select distinct values but it is now doing it for every cell in the range instead of grouping. at Kit and Lot for each record. (if that makes sense... the code is below) I want it to select the distinct value on A2...E2 and loop but instead it is going down each column and returning the distinct values in each column as a list item.

Private Sub UserForm_Initialize()
Dim PickList As Collection
Dim PickRange As Range
Dim ws As Worksheet
Dim MyVAL As Variant

Set ws = Sheets("Sheet4")
Set PickRange = ws.Range("a2", ws.Range("e2").End(xlDown))

Set myList = New Collection
On Error Resume Next
For Each mycell In PickRange.Cells
myList.ADD mycell.Value, CStr(mycell.Value)
Next mycell
On Error GoTo 0

For Each MyVAL In myList
Me.ListBox1.AddItem MyVAL
Next MyVAL

End Sub