Problems to Populate a Multiple Listbox with non contiguous columns

[purinqui]

Hello everyone, a big greeting to everyone before anything else.

I am having a problem with the handling of a Multiple Listbox.

I need to populate a Multiple Listbox from a sheet containing 3 columns but I need it to only load the information
column 1 and 3 only, avoiding column 2.

I have made the code work but with continuous columns.
But when I want to load two columns that are not continuous, the code does not allow me to populate the multiplelistbox

This is the sheet I have, and I need only the information in column A and C to be loaded into the MultipleListbox.

When I run the macro it doesn't load anything in the multiplelistbox.

Try to do it with this code but when the multiplelistbox is opened it doesn't load any information:

Option Explicit
Dim rng1 As Range, rng2 As Range, rng As Range

Private Sub CommandButton1_Click()
rng.Sort rng.Cells(1), 1, Header:=xlNo
End Sub

Private Sub CommandButton2_Click()
rng.Sort rng.Cells(1), 3, Header:=xlNo
End Sub

Private Sub CommandButton3_Click()

With rng.Columns(1)
  .Formula = "=Today() - RandBetween(0, 1100)"
  .Calculate: .Value = .Value
End With

End Sub

Private Sub UserForm_Initialize()

Dim ws As Worksheet

Set ws = Worksheets("Sheet1")


Set rng1 = ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
Set rng2 = ws.Range("C1:C" & ws.Range("C" & ws.Rows.Count).End(xlUp).Row)

Set rng = Union(rng1, rng2)

CommandButton3_Click
ListBox1.RowSource = rng.Address(external:=True)
End Sub

Note: I use the Union Method to join two non-contiguous columns.

Thank you in advance for any contribution you can give. Regards.

Link of Woorkbook

 

[purinqui]

Link to Download the file

 

[Rick Rothstein]

You can use this UserForm_Initialize event code to populate the ListBox...

Private Sub UserForm_Initialize()
  Dim X As Long, Arr As Variant
  Arr = Application.Index(Cells, Evaluate("ROW(1:" & Cells(Rows.Count, "A").End(xlUp).Row & ")"), [{1,3}])
  For X = 1 To UBound(Arr)
    Arr(X, 1) = Format(Arr(X, 1), "m/d/yyyy")
  Next
  ListBox1.List = Arr
End Sub

 

[purinqui]

You can use this UserForm_Initialize event code to populate the ListBox...

Private Sub UserForm_Initialize()
  Dim X As Long, Arr As Variant
  Arr = Application.Index(Cells, Evaluate("ROW(1:" & Cells(Rows.Count, "A").End(xlUp).Row & ")"), [{1,3}])
  For X = 1 To UBound(Arr)
    Arr(X, 1) = Format(Arr(X, 1), "m/d/yyyy")
  Next
  ListBox1.List = Arr
End Sub

Thank you very much, it works perfectly!!. Now i have the Multiple Listbox Populated.

One more question about it.

I need to sort the Multiple ListBox. (High to Low and Low to High.)

I did this action for sort with this code

Private Sub UserForm_Initialize()

Dim ws As Worksheet

Set ws = Worksheets("Sheet1")


Set rng1 = ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
Set rng2 = ws.Range("C1:C" & ws.Range("C" & ws.Rows.Count).End(xlUp).Row)

Set rng = Union(rng1, rng2)

End Sub


Private Sub CommandButton1_Click()
rng.Sort rng.Cells(1), 1, Header:=xlNo
End Sub

But now the vba show me an error when a run it. "This cant be done on a multiple range selection, select a single range and try again".

How can i sort this multiple listbox taking into account one of the columns has dates as information.

Thank you very much. Regard