Remove duplicates from a cascading combobox

DanSMT

Board Regular
Joined
Sep 13, 2019
Messages
99
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 shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,297
Office Version
  1. 2010
Platform
  1. Windows
what type of values are stored in "B" numbers? text?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
 

DanSMT

Board Regular
Joined
Sep 13, 2019
Messages
99

ADVERTISEMENT

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?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
No idea what you mean by that. It does the same thing your original code did, but should be a lot quicker.
 

DanSMT

Board Regular
Joined
Sep 13, 2019
Messages
99

ADVERTISEMENT

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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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".
 

DanSMT

Board Regular
Joined
Sep 13, 2019
Messages
99
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,569
Messages
5,637,098
Members
416,957
Latest member
Brovashift

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
Top