Excel VBA Multicolumn Listbox add non contiguous range

Question

Im trying to figure out how to add a non contiguous range of cells in a row to a multicolumn Listbox, but only the second line below works.

lbxSelectable.List = Sheets("DB").Range("A1,B1,C1").Value
lbxSelectable.List = Sheets("DB").Range("A1:C1").Value

Is there any efficient function which will allow me to select certain columns in a row and stick it in an entry of a multicolumn list box?

Many thanks

Answered an old question, but demonstrate a tricky way to populate a listbox with non contiguous column data without any loop using rather unknown features of the ►Application.Index method :-) @Gelion — T.M.
– T.M., Commented Oct 24, 2018 at 21:04

Siddharth Rout · Accepted Answer · 2012-05-16 16:41:49Z

3

To display a non contiguous range of cells in a row to a multicollumn Listbox, you have to first create an Array and then assign it to the .list of the listbox. Here is an example.

Option Explicit

Private Sub CommandButton1_Click()
    Dim Ar() As String
    Dim rng As Range, cl As Range
    Dim i As Long

    Set rng = Range("A1,C1,E1")

    i = 1

    For Each cl In rng
        ReDim Preserve Ar(1, 1 To i)
        Ar(1, i) = cl.Value
        i = i + 1
    Next

    With ListBox1
        .ColumnCount = i - 1
        .ColumnWidths = "50;50;50"
        .List = Ar
    End With
End Sub

SNAPSHOT

enter image description here

answered May 16, 2012 at 16:41

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

1 Comment

T.M. Over a year ago

Though this is an elder post, maybe your are interested in a tricky way to populate a listbox with non contiguous column data without any loop :-) @SiddharthRout

T.M. · Accepted Answer · 2018-10-24 20:41:39Z

I. Display non contiguous column data without loops

In addition to Siddharth's valid solution I demonstrate a flexible approach without any loop necessity. Furthermore I added the possibility to display not only one, but even more data rows as well as to define a varying start row (e.g. m=2 in this example:-).

Applied method step by step

After fully qualifying a range reference in section [0], defining start row and end row in section [1], section [2] does the main work by assigning the wanted columns to a variant datafield array using advanced filtering possibilities of the ► Application.Index method •):

a) First it gets the entire data range content from top row 1 to right column E,
b) eventually starts from 2nd row m (i.e. omitting title row) to lastrow n and
c) finally filters columns 1,3,5 (= A ,C, E) via the Application.Index method.

Section [3] fills the listbox with data from the array which have to be 2-dimensional. If data sets have more than one item 'rows' they get assigned to the ►.List property of the listbox - cf. section [3]b).

If, however there is only one single data row, the array v would loose one needed dimension, so it has to be transposed via the Application.Transpose method (changing rows and columns, but regaining the 2nd dimension!) and then assigned to the listboxes ►.Column property.

•) cf. Insert first column in datafield array without loops or API calls

Option Explicit                                    ' declaration head of code module

Private Sub CommandButton1_Click()
' [0] Declare variables start row, lastrow, (variant) datafield array, worksheet   
      Dim m&, n&, v, ws As Worksheet               ' data types Long, Long, Variant, WorkSheet
      Set ws = ThisWorkbook.Worksheets("mySheet")  ' << change to your sheet name
' [1] define Start Row m and Last Row n (based on items in column A)
      m = 2: n = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
' [2] assign data to variant datafield array
      v = Application.Index(ws.Range("A1:E" & n).Value2, Evaluate("row(" & m & ":" & n & ")"), Array(1, 3, 5))
' [3] fill ListBox with array contents
  With ListBox1
      .ColumnWidths = "50;50;50"
      If m = n Then                         ' Case a) SINGLE data row only (needs transposed data)
         .ColumnCount = UBound(v, 1)        '         counts 1st dimension of v
         .Column = Application.Transpose(v) '      << assign transposed array v to listbox COLUMN property
      Else                                  ' Case b) several data rows
         .ColumnCount = UBound(v, 2)        '         counts 2nd dimentson of v
         .List = v                          '      << assign array v to listbox LIST property
      End If
  End With
End Sub

II. Simple alternative with adapted ColumnWidths

Of course, it would be possible to collect the whole data range columns A:E, but to define zero widths for the columns to be omitted in display via the ColumnWidths property:

With ListBox1
  .ColumnWidths = "50;0;50;0;50"
  ' ...
End With

Enjoy it :-)

Collectives™ on Stack Overflow

Excel VBA Multicolumn Listbox add non contiguous range

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related