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

Question

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!

Doug Glancy · Accepted Answer · 2013-12-28 19:01:23Z

1

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

edited Dec 28, 2013 at 19:01

answered Dec 26, 2013 at 22:05

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

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

2 Comments

MattB Over a year ago

This is great! I had no idea index could be used in this way! Very, very handy indeed. Thank you for the tip!

Doug Glancy Over a year ago

I'm glad it helped! I do love the way Index works with this. I did realize that I made a silly mistake that I've made before, i.e., thinking I needed two next Application.Transposes to coerce the Range to a List, when I could just specify the Value property of the Range. I've edited to correct this.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related