Remove duplicates from a cascading combobox

DanSMT

Board Regular
Joined
Sep 13, 2019
Messages
203
Office Version
  1. 2013
Platform
  1. Windows
All,

I've completed the task I would like to achieve, however, I am wondering if it is possible to complete the task of actively removing duplicates from a cascading combobox without creating a new sheet in the workbook. This appears to slow down UI operation quite drastically.

VBA Code Below;

VBA Code:
Private Sub combobox7_Change()
Dim myval As String
Dim lr As String
Dim x As Long

myval = Me.ComboBox7

'loop thru col a
lr = ThisWorkbook.Sheets("paste log").Cells(Rows.Count, 1).End(xlUp).Row

'clear combobox 11
Me.ComboBox11.Clear

For x = 2 To lr
If myval = ThisWorkbook.Sheets("paste log").Cells(x, 1) Then
'add to combobox
Me.ComboBox11.AddItem ThisWorkbook.Sheets("paste log").Cells(x, "b")
End If
Next x

Dim iCntr As Long
Dim recCountBefore As Long
Dim lRow As Long
Dim tmpsht As Worksheet

recCountBefore = Me.ComboBox11.ListCount

Set tmpsht = ThisWorkbook.Worksheets.Add

For iCntr = 0 To recCountBefore - 1
    tmpsht.Cells(iCntr + 1, 1) = Me.ComboBox11.List(iCntr)
Next

tmpsht.Columns(1).RemoveDuplicates Columns:=Array(1)

lRow = tmpsht.Range("A60000").End(xlUp).Row

ComboBox11.Clear
For iCntr = 1 To lRow
    ComboBox11.AddItem tmpsht.Cells(iCntr, 1)
Next

Application.DisplayAlerts = False
    tmpsht.Delete
Application.DisplayAlerts = True

End Sub
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
what type of values are stored in "B" numbers? text?
 
Upvote 0
Try something like this:

VBA Code:
Private Sub combobox7_Change()
    Dim myval As String
    Dim lr As String
    Dim x As Long
    
    myval = Me.ComboBox7
    With ThisWorkbook.Sheets("paste log")
        'last row in col A
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        
        Dim data
        data = .Range("A2:B" & lr).Value
    End With
    With CreateObject("Scripting.Dictionary")
        For x = LBound(data) To UBound(data)
            If myval = data(x, 1) Then
                .Item(data(x, 2)) = Empty
            End If
        Next x
        ComboBox11.List = .keys
    End With

End Sub
 
Upvote 0
Solution
Try something like this:

VBA Code:
Private Sub combobox7_Change()
    Dim myval As String
    Dim lr As String
    Dim x As Long
   
    myval = Me.ComboBox7
    With ThisWorkbook.Sheets("paste log")
        'last row in col A
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
       
        Dim data
        data = .Range("A2:B" & lr).Value
    End With
    With CreateObject("Scripting.Dictionary")
        For x = LBound(data) To UBound(data)
            If myval = data(x, 1) Then
                .Item(data(x, 2)) = Empty
            End If
        Next x
        ComboBox11.List = .keys
    End With

End Sub
Works perfect!! thanks!

As I have not run many iterations yet, will this maintain parent child between columns?
 
Upvote 0
No idea what you mean by that. It does the same thing your original code did, but should be a lot quicker.
 
Upvote 0
No idea what you mean by that. It does the same thing your original code did, but should be a lot quicker.
If i need to maintain relationship between the information in column 1 and column 2 based on the row
 
Upvote 0
As I said, it does exactly what your original code did - i.e. iterates column A, checking to see if it matches the specified value; if so, it loads column B into the combobox if it's not already there. There isn't really a link between columns A and B from that point onwards, so I don't know what you mean by "maintain relationship".
 
Upvote 0
As I said, it does exactly what your original code did - i.e. iterates column A, checking to see if it matches the specified value; if so, it loads column B into the combobox if it's not already there. There isn't really a link between columns A and B from that point onwards, so I don't know what you mean by "maintain relationship".
It's ok, ill run it as such and see if it works as intended. Thank you again for the assistance!!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top