CODEWITHSUNDEEP
excelvbalistbox
Gelion
Gelion

Reputation: 531

Excel VBA Multicolumn Listbox add non contiguous range

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

Upvotes: 2

Views: 6456

Answers (2)

T.M.
T.M.

Reputation: 9948

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 :-)

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149295

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

Upvotes: 3

Related Questions