Unique list in Listbox VBA

[lwarren30]

I have a data sheet that has multiple entries for Batch Number. Meaning a batch will have multiple expense entries but there will be only one batch number for those. Example: A receipt would be a batch and the items bought would be the entries. I want to create a userform with a listbox to display only the batch numbers (a unique list) in vba. I know how to do it with formulas but rather not because it requires more data.

How can I create a unique list of batch numbers and data related to it in a userform in VBA? And can this be sortable by date?

Thank you.

 

[Yongle]

I want to create a userform with a listbox to display only the batch numbers (a unique list)

Assumes batch numbers are in column A, starting at row 2 in sheet called "SheetName" and are placed in ListBox1
- amend to match your data

Add this to your UserForm code

Private Sub GetUniqueList()
    Dim sh As Worksheet:        Set sh = Sheets("[COLOR=#ff0000]SheetName[/COLOR]")
    Dim rng As Range:           Set rng = sh.Range("[COLOR=#ff0000]A2[/COLOR]", sh.Range("[COLOR=#ff0000]A[/COLOR]" & Rows.Count).End(xlUp))
    Dim cel As Range, Batch As Variant, Batches As New Collection
    
    On Error Resume Next
        For Each cel In rng
            Batches.Add cel, cel
        Next cel
    On Error GoTo 0
    
    For Each Batch In Batches
       [COLOR=#ff0000] ListBox1[/COLOR].AddItem Batch
    Next Batch
End Sub

To call it...

Private Sub UserForm_Initialize()
    GetUniqueList
End Sub

After you get this working
- let me know how you get on and I can help you pull the related data into the userform

The method is
- sort your data table by date
- filter data based on value selected in listbox
- import those values into userform

To make life easier ...
- what are the names of the listbox and data sheet?
- does data table start in cell A1 with data from row 2 onwards? If not, please specify

Are you wanting to do anything with the data other than looking at it in the userform?

 

[lwarren30]

That worked perfectly!

Listbox = lst_RB_BatchView
Data Sheet = Financial_Input, Sheet12

The data starts in A2 but batch number starts in column B and downward. Row 1 are headers that I would like to be in the Listbox too. I want to be able to sort by date and batch type too.

After I get the batch data in the listbox I want to populate in another userform (already created) for editing and datasheet updating. I shouldn't have issues with that as I've done something similar in the past.

Thank you!

 

[Yongle]

Ok will look at this tomorrow
which column contains the Date ?

 

[lwarren30]

Column B - V.

 

[Yongle]

So you want to sort on batches in column B and on dates in column V?

 

[lwarren30]

Yes, sort by batches in column B for all data in related rows through column V.

 

[lwarren30]

I'm not familiar with Collections but I know they can be very useful. Let me look them up and see if I can figure out where you are going with this.

 

[Yongle]

Using a collection is a quick (lazy?) way to create the unique list.
Collections do not allow duplicate "keys"

Batches.Add cel cel

The first "cel" is the item to be added, the second "cel" is the "key".
Duplicate "keys" are not allowed and causes the VBA to fail - resulting in a duplicate "item" not being added to the collection

That was the only reason for using a collection - it avoided having to remove duplicates values a different way

 

[lwarren30]

Seems to be a bit more to call it lazy. But I've never understood arrays.