MichaelRSnow
Active Member
- Joined
- Aug 3, 2010
- Messages
- 409
Hi
I've been searching the forums for some vba code which takes a visible range of cells/columns from a worksheet and places them within a Listbox held within a UserForm
The below code appears to do part of that (product_baseline is the spreadsheet, ShowProducts is the ListBox)
Issue 1: The below is it's not visible cells, it copies everything? any ideas how to apply the .SpecialCells(12) logic to the below?
Issue 2: Appears to cap at 10 columns, if i add a further "ShowProducts.List(i - 1, 10) = .Cells(i, 11).Value" to the code it fails with an could not set the list property value error?
I've been searching the forums for some vba code which takes a visible range of cells/columns from a worksheet and places them within a Listbox held within a UserForm
The below code appears to do part of that (product_baseline is the spreadsheet, ShowProducts is the ListBox)
Issue 1: The below is it's not visible cells, it copies everything? any ideas how to apply the .SpecialCells(12) logic to the below?
Issue 2: Appears to cap at 10 columns, if i add a further "ShowProducts.List(i - 1, 10) = .Cells(i, 11).Value" to the code it fails with an could not set the list property value error?
VBA Code:
Dim cell As Range
Dim MyArr As Variant, i As Long
' intialize array to high number of elements at start
ShowProducts.ColumnWidths = "38.25;80.75;153;68;110.5;97.75;246.5;28.5;28.5"
ShowProducts.Clear 'CLEAR THE LIST BOX BEFORE REBUILDING THE LIST
With Product_baseline
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row 'FIND THE LAST ROW NUMBER
For i = 1 To LastRow 'ADD ITEMS TO THE LISTBOX
ShowProducts.AddItem .Cells(i, 1).Value 'ADD NEW ROW
ShowProducts.List(i - 1, 1) = .Cells(i, 2).Value 'ADD TO ADJACENT COLUMNS ON SAME ROW
ShowProducts.List(i - 1, 2) = .Cells(i, 3).Value
ShowProducts.List(i - 1, 3) = .Cells(i, 4).Value
ShowProducts.List(i - 1, 4) = .Cells(i, 5).Value
ShowProducts.List(i - 1, 5) = .Cells(i, 6).Value
ShowProducts.List(i - 1, 6) = .Cells(i, 7).Value
ShowProducts.List(i - 1, 7) = .Cells(i, 8).Value
ShowProducts.List(i - 1, 8) = .Cells(i, 9).Value
ShowProducts.List(i - 1, 9) = .Cells(i, 10).Value
Next i
End With