Fill Listbox with 2 sheet columns.

Hi there,

I'm trying to populate a Listbox "listbox3" with two columns on a worksheet. These columns aren't set next to one another, therefore I cannot just assign a rowsource of "A:B" etc.. it would instead need to be "D" and "F".

I have started a piece of code (below) however not sure if I'm going about it the right way. Any advice would be greatly appreciated.

 Dim rngFirstNme As Range
Dim rngLastNme As Range

rngFirstNme = Sheets("pgs_CustomerList_Unique").Range("D2:D502").Value

rngLastNme = Sheets("pgs_CustomerList_Unique").Range("F2:F502").Value


Listbox3. ? = ?

It is simplest to assign the three columns to the list box and make the second column width 0. You may also create an array from the two columns and assign it to the List property of the Listbox but I do not see benefit over simply using three columns.

Example of which option?

Try this.

Private Sub Userform_Initialize()
Dim arr As Variant

      arr = Sheets("pgs_CustomerList_Unique").Range("D2:F502").Value
      With ListBox2
             .ColumnCount = 3
             .ColumnWidths = "50;0;50"
             .List = arr
     End With

End Sub

Norie has given example for three columns with one hidden. If you wish array from two columns you may use code like this:

ListBox2.ColumnCount = 2
ListBox2.List = Application.Index(Sheets("pgs_CustomerList_Unique").Range("D2:F502"), [Row(1:501)], array(1, 3))

Izandol

I don't think you need the row argument, as far as I know if you leave it out INDEX will return the entire column(s) from the reference range.

Norie

If you pass array for third argument you must use array for second - using 0 or omitting does not work unfortunately.