In VBA code,how to add values to a listbox using for each cell approach with defining a range End(XlDown)?

Question

I have created a macro file with Forms and Word to Excel.

In this coding fewthings are not working as per my expectation.

Get unique Employee Name from Excel data base. I want to add unique employee names from excel database and get is saved in a sheet. After that those values to be added to list box. Here i cannot define a range like "A1:A10".. I want to choose the data from A1 to end data.

If for each cell approach will not work, please help in do while approach

I need help in defining the range and code given below

ListEmployeeName.Clear
For Each cell In Worksheets("SunEmployeeDetails").Range("A1").End(xlDown)
    ListEmployeeName.AddItem (cell.Value)
Next
    ListEmployeeName.Value = Worksheets("SunEmployeeDetails").Range("A1")
End Sub

ListEmployeeName.Clear Worksheets("SunEmployeeDetails").Select Range("A1").Select Do While ActiveCell.Value <> "" ListEmployeeName.AddItem (cell.Value) ActiveCell.Offset(1, 0).Select Loop ListEmployeeName.Value = Worksheets("SunEmployeeDetails").Range("A1") End Sub This do while approach is also not working — sundar
– sundar, Commented Dec 25, 2019 at 18:45
Avoid the use of .Select/Activecell etc... you may want to see How to avoid using Select in Excel VBA. I have posted an answer. you may have to refresh the page to see it — Siddharth Rout
– Siddharth Rout, Commented Dec 25, 2019 at 18:53
This could be a cool alternative in your case to get some uniques — JvdV
– JvdV, Commented Dec 25, 2019 at 20:37

Siddharth Rout · Accepted Answer · 2019-12-25 18:52:43Z

1

Find Last Row and then define your range Range("A1:A" & LastRow)

You can also find the last row and loop through the range using a For loop. Also to get unique Employee Name, you can use On Error Resume Next with a Collection as shown below. I have commented the code below so you should not have a problem understanding it. But if you do then simply ask.

Is this what you are trying? (Untested).

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, i As Long
    Dim col As New Collection
    Dim itm As Variant

    Set ws = Worksheets("SunEmployeeDetails")

    With ws
        '~~> Find Last row
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Loop through the range and add it to the unique
        '~~> collection using "On Error Resume Next"
        For i = 1 To lRow
            On Error Resume Next
            col.Add .Range("A" & i).Value, CStr(.Range("A" & i).Value)
            On Error GoTo 0
        Next i
    End With

    ListEmployeeName.Clear

    '~~> add the itme from collection to the listbox
    For Each itm In col
        ListEmployeeName.AddItem itm
    Next itm
End Sub

answered Dec 25, 2019 at 18:52

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JvdV Over a year ago

Pretty sure you can get a 1D array assigned to a listbox in one go right? Using .List property..... Btw, not used to see untested codes from your end =)

Siddharth Rout Over a year ago

@JvdV: Oh yes. I have shown that Here as well but did not mention here becuase it would be an overkill after trying to get a unique collection.

JvdV Over a year ago

Nice linked post. Didn't know about a bug. I'll have a bash with that combined with what I linked to earlier

JvdV · Accepted Answer · 2019-12-26 09:03:22Z

Here is my take on it, techniques taken from here:

_{Methode 1: Using a dictionary}

Dim lr As Long, x As Long
Dim arr As Variant
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

With Sheet1 'Change accordingly

    'Find the last used row
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    arr = .Range("A1:A" & lr).Value

End With

'Loop through memory and fill dictionary
For x = LBound(arr) To UBound(arr)
    dict(arr(x, 1)) = 1
Next x

'Add array to Listbox
Me.ListEmployeeName.List = dict.Keys

_{Methode 2: Using Evaluation}

Dim lr As Long
Dim arr As Variant

With Sheet1 'Change accordingly

    'Find the last used row
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row

    'Get array of unique values
    arr = Filter(.Evaluate("TRANSPOSE(If(Row(A1:A" & lr & ")<>MATCH(A1:A" & lr & ",A1:A" & lr & ",0),""|"",A1:A" & lr & "))"), "|", False)

    'Add array to Listbox
    Me.ListEmployeeName.List = arr

End With

Collectives™ on Stack Overflow

In VBA code,how to add values to a listbox using for each cell approach with defining a range End(XlDown)?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related