CODEWITHSUNDEEP
excelvba
JoshL
JoshL

Reputation: 164

ListBox has multiple of the same values

I am using a selection from a previous listbox 'HousingTypeList' to determine what will be in the next listbox 'CatalystDiameterList' by searching through a table.

The data that is added to the 'CatalystDiameterList' is contained in row 'H' (starting at H6).

My code so far does its job nicely; however, it pulls multiple of the same values so I have a long list of many of the same values.

Here's my code so far, I was hoping to be able to alter this a little to make it work but so far I haven't been able to.

Private Sub HousingTypeList_Click()

lastrow = Sheet2.Cells(Rows.Count, 5).End(xlUp).Row

curVal = Me.HousingTypeList.Value

For x = 6 To lastrow
    If Worksheets("FSC PSC PFC").Cells(x, "B") = curVal Then
        Me.CatalystDiameterList.AddItem Worksheets("FSC PSC PFC").Cells(x, "H")
    End If
Next x

End Sub

I am open to editing this code to prevent multiples or adding something in that filters through and takes out multiples after they are added.

Any/All help is greatly appreciated!

Thank you all!

Upvotes: 0

Views: 46

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Create a Dictionary object like in the next code and replace your loop with the next one:

Dim dict As Object
  Set dict = CreateObject("Scripting.Dictionary")
  For x = 6 To lastRow
    If Worksheets("FSC PSC PFC").Cells(x, "B") = curVal Then
        If Not dict.Exists(Worksheets("FSC PSC PFC").Cells(x, "H").value) Then
            Me.CatalystDiameterList.AddItem Worksheets("FSC PSC PFC").Cells(x, "H")
            dict(Worksheets("FSC PSC PFC").Cells(x, "H").value) = 1
        End If
    End If
Next x

Upvotes: 2

Related Questions