SamirBhowmik
New Member
- Joined
- Sep 23, 2021
- Messages
- 26
- Office Version
- 365
- 2013
- Platform
- Windows
Hello,
I am new to vba, I have a userform with multiple listboxes where the data are from a table called"DynamicPath_2". Attached is a screenshot of the userform.
For the below code which I managed to make work for me for the first ListBox1, can anyone help me to modify the code to populate all the listBoxes with only unique values from the table columns. Also if possible to fasten the execution because currently this code is already slowing the loading of the userform, I imagine it would afftect more if I complete the code for 10 listboxes : (
Thank you
I am new to vba, I have a userform with multiple listboxes where the data are from a table called"DynamicPath_2". Attached is a screenshot of the userform.
For the below code which I managed to make work for me for the first ListBox1, can anyone help me to modify the code to populate all the listBoxes with only unique values from the table columns. Also if possible to fasten the execution because currently this code is already slowing the loading of the userform, I imagine it would afftect more if I complete the code for 10 listboxes : (
VBA Code:
Private Sub UserForm_Initialize()
Dim UniqueList() As String
Dim X As Long
Dim Rng1, Rng2 As Range
Dim c As Range
Dim Unique As Boolean
Dim Y As Long
Set Rng1 = Sheets("MDB").Range("A2:A60000")
Set Rng2 = Sheets("MDB").Range("C2:C60000")
Y = 1
ReDim UniqueList(1 To Rng1.Rows.Count)
ReDim UniqueList(1 To Rng2.Rows.Count)
For Each c In Rng1
If Not c.Value = vbNullString Then
Unique = True
For X = 1 To Y
If UniqueList(X) = c.Text Then
Unique = False
End If
Next
If Unique Then
Y = Y + 1
Me.ListBox1.AddItem (c.Text)
UniqueList(Y) = c.Text
End If
End If
Next
For Each c In Rng2
If Not c.Value = vbNullString Then
Unique = True
For X = 1 To Y
If UniqueList(X) = c.Text Then
Unique = False
End If
Next
If Unique Then
Y = Y + 1
Me.ListBox2.AddItem (c.Text)
UniqueList(Y) = c.Text
End If
End If
Next
End Sub
Thank you