zoom38
01-16-2018, 08:45 PM
Good evening, does anyone know if it is possible to add an unequal amount of data to two columns of the same userform listbox? Ex. Six cells of data from sheet one into Column 1 and ten cells of data from sheet 2 into Column 2.
I've been working with the code below but can't get it right. My code only allow the same number of records in each column.
Sub TList2()
Dim LastRow As Integer
Dim LastRow1 As Integer
Dim LastRow2 As Integer
Dim MyList1 As Variant
Dim MyList2 As Variant
Dim i As Long
Sheets("Sheet1").Activate
LastRow1 = Sheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row
Sheets("Sheet2").Activate
LastRow2 = Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp).Row
Sheets("Sheet1").Activate
MyList1 = Sheets("Sheet1").Range(Cells(6, 3), Cells(LastRow1, 3)).Value
Sheets("Sheet2").Activate
MyList2 = Sheets("Sheet2").Range(Cells(6, 3), Cells(LastRow2, 3)).Value
CurrentT = (LastRow1 - 5) + (LastRow2 - 5)
With List.ListBox1
.ColumnCount = 2
.ColumnWidths = "130;130"
For i = 1 To LastRow1 - 5
.AddItem MyList1(i, 1)
.List(.ListCount - 1, 1) = MyList2(i, 1)
Next i
'For i = 1 To LastRow2 - 5
'.List(.ListCount - 1, 1) = MyList2(i, 1)
'Next i
.Font.Name = "Times New Roman"
.Font.Size = 12
End With
End Sub
Attached is a small spreadsheet as the example.
Thanks in advance.
Gary
I've been working with the code below but can't get it right. My code only allow the same number of records in each column.
Sub TList2()
Dim LastRow As Integer
Dim LastRow1 As Integer
Dim LastRow2 As Integer
Dim MyList1 As Variant
Dim MyList2 As Variant
Dim i As Long
Sheets("Sheet1").Activate
LastRow1 = Sheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row
Sheets("Sheet2").Activate
LastRow2 = Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp).Row
Sheets("Sheet1").Activate
MyList1 = Sheets("Sheet1").Range(Cells(6, 3), Cells(LastRow1, 3)).Value
Sheets("Sheet2").Activate
MyList2 = Sheets("Sheet2").Range(Cells(6, 3), Cells(LastRow2, 3)).Value
CurrentT = (LastRow1 - 5) + (LastRow2 - 5)
With List.ListBox1
.ColumnCount = 2
.ColumnWidths = "130;130"
For i = 1 To LastRow1 - 5
.AddItem MyList1(i, 1)
.List(.ListCount - 1, 1) = MyList2(i, 1)
Next i
'For i = 1 To LastRow2 - 5
'.List(.ListCount - 1, 1) = MyList2(i, 1)
'Next i
.Font.Name = "Times New Roman"
.Font.Size = 12
End With
End Sub
Attached is a small spreadsheet as the example.
Thanks in advance.
Gary