Non-Contiguous range to array to populate multicolumn ListBox...

AndyPandy27

Board Regular
Joined
Jul 17, 2012
Messages
138
...so a nice and simple question!

Hi All,

I've been putting together a Userform, which is designed to cherry-pick a selection of records (rows) from a worksheet, and use the data contained in each cherry-picked record to populate a multicolumn ListBox.

My key issues are:

1) The records (rows) I want to select from the worksheet are based on a specific criterion (not every single row).
2) I need 11 columns in my ListBox, which means I cannot use the .AddItem option.

Having run into the issue described in point 2 above, I have discovered that the way forward is to define an array, based on the criterion needed, and then assign that array to the .List option. However, I don't know how to do this, which is where you delightful people come in - please can you help!

The code I am using at the moment, which works for i = 1 to 9, but not for i = 1 to 10, is shown below:

Code:
Private Sub Populate_Open()
    LastRow = ThisWorkbook.Worksheets("Pipeline").Range("A" & Rows.Count).End(xlUp).Row
    CapitalPipeline.PipelineOpen.Clear
    For Each MyCell In ThisWorkbook.Worksheets("Pipeline").Range("C2:C" & LastRow)
        If MyCell.Value = "Open" Then
            With CapitalPipeline.PipelineOpen
                .AddItem MyCell.Offset(, -2).Value
                For i = 1 To 9
                    .List(.ListCount - 1, i) = MyCell.Offset(, i - 2).Value
                Next i
                .List(.ListCount - 1, 7) = Format(MyCell.Offset(, 5).Value, "£#,###")
                '.List(.ListCount - 1, 10) = MyCell.Row
            End With
        End If
    Next MyCell
End Sub

> The Userform is called "CapitalPipeline"
> The ListBox is called "PipelineOpen"
> The worksheet containing the data is called "Pipeline"
> The column containing the criterion is Column C
> I want to run through every row in "Pipeline", and if the value in Column C is "Open", I want the values from Columns A:K to be used for Columns 1 to 11 of the ListBox (or Columns 0 - 10 using the ListBox numbering).

Can the above code be adapted to create an array based on the details listed above?

Any help would be greatly appreciated.

Many thanks,

AP
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,180
Office Version
  1. 365
Platform
  1. Windows
Have you thought about using RowSource and setting it by a filter?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,139
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
This should do what you want...
Code:
Private Sub UserForm_Click()
  CapitalPipeline.PipelineOpen.List = Application.Index(Worksheets("Pipeline").Cells, Application.Transpose(Split(Application.Trim(Join(Application.Transpose(Evaluate(Replace("IF(Pipeline!C1:C#=""Open"",ROW(Pipeline!C1:C#),"" "")", "#", Sheets("Pipeline").Cells(Rows.Count, "C").End(xlUp).Row))))))), Split("1,2,3,4,5,6,7,8,9,10,11", ","))
End Sub
 

AndyPandy27

Board Regular
Joined
Jul 17, 2012
Messages
138
Hi Rick,

This worked perfectly - thank you so much.

Is there any chance you might be happy to run through the details of how your code works at all? I've not seen some of the elements you have used before (e.g. "Join", the "#" used in the range and "Split") - so some explanation of how this works overall would be very much appreciated.

Thanks again for your help - incredible solution from this forum, as always!

Cheers,

AP
 

Forum statistics

Threads
1,136,258
Messages
5,674,670
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top