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

[AndyPandy27]

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

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

 

[Kenneth Hobson]

Have you thought about using RowSource and setting it by a filter?

 

[Rick Rothstein]

This should do what you want...
[table="width: 500"]
[tr]
[td]

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

[/td]
[/tr]
[/table]

 

[AndyPandy27]

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