Selectively populating a multicolumn listbox

[JimmyBob]

Hi folks. I have a multicolumn listbox (8 columns). Is it possible to selectively populate only Listbox columns 1-4 with e.g. C1:F30 from a spreadsheet using the Sheets .Range property? I want to bypass column 0.

The following, by default, populates columns 0-3, can I change this to populate columns 1-4 in a similar way?

With ListBox1
.ColumnCount = 8
.List = Sheets("Sheet1").Range("C1:F30").Value
End With

I could use other methods to populate the listbox e.g. .additem or .list(row,column)= ,but I'm interested to see if I can populate it in one step.

I'll be using the remaining columns to display various things such as debug info relating to each record/row

Thanks.

 

[Jerry Sullivan]

Hi JimmyBob,

You could build a variant array that would add an empty column(s) to the left of the data,
then populate the ListBox using that array.

Sub PopulateListBox()
    Dim vArray As Variant
    Dim lOffsetCols as Long

    vArray = Sheets("Sheet1").Range("C1:F30").Value

    
    With ListBox1
        .ColumnCount = 8
        .List = OffsetArray(vArray, 1) 'offset 1 column
    End With

 
End Sub

Function OffsetArray(vArrIn As Variant, lOffsetCols) As Variant
'---resizes array and offsets to add variable number of blank columns to a variant array

    Dim iR As Long, iC As Long
    Dim vArrOut As Variant

    
    ReDim vArrOut(1 To UBound(vArrIn, 1), 1 To UBound(vArrIn, 2) + lOffsetCols)

    
    For iR = 1 To UBound(vArrIn, 1)
        For iC = 1 To UBound(vArrIn, 2)
              vArrOut(iR, iC + lOffsetCols) = vArrIn(iR, iC)
        Next iC
    Next iR

    
    OffsetArray = vArrOut
End Function

 

[JimmyBob]

Thanks for this, Jerry, a great solution - much appreciated!