CODEWITHSUNDEEP
arraysvbacollectionslistbox
MattB
MattB

Reputation: 2241

Loop through the rows of a multi-column listbox in VBA

I've got a listbox issue. I'm attempting to copy the contents of a multi-column listbox to a collection as arrays of each row of the listbox. I realize I could loop through each row and column of the listbox, but I was trying to find a way to return each row of the listbox and assign it directly to the array. Something like this:

For each item in ListBox
    tempArrayA = item
    tempCollection.add item
Next item

However, when I do that it gives me each individual field of each row rather than the entier row all at once as I'd like it to do. I've been googling, and I'll keep googling, but I just felt it would be nice to do this by iterating through each item in the listbox rather than selecting each row and then itterating through each column of that row since I have other code that executes upon selection of a row of the listbox.

Any ideas are, as always, greatly appreciated. Thanks!

Upvotes: 1

Views: 2944

Answers (1)

Doug Glancy
Doug Glancy

Reputation: 27478

You can take advantage of the worksheet Index function, the fact that it works on arrays, and the fact that a 0 entered as its Column argument grabs the whole row. It's really cool!:

Private Sub UserForm_Activate()
Dim varList As Variant
Dim varRow As Variant
Dim i As Long
Dim tempCollection As Collection

Me.ListBox1.List = ActiveSheet.Range("A2:B20").Value
varList = Me.ListBox1.List
Set tempCollection = New Collection
For i = 1 To Me.ListBox1.ListCount
    varRow = Application.WorksheetFunction.Index(varList, i, 0)
    tempCollection.Add varRow, CStr(i)
    Debug.Print tempCollection(i)(1); " "; tempCollection(i)(2)
Next i
End Sub

Upvotes: 1

Related Questions