Solved: Selecting only certain rows to use in Combobox list

[zagrijs]

I want to put the values of only certain rows (4 columns) into a a combobox as list.

Using a multi column array works fine.

However, the number of rows may be different each time the sub is called. It wil remain constant until the next time it is called.

VBA does not allow one to use a variable to declare the number of rows in the array. It does allow the use of a constant, but again does not allow one to store the value of variable in a constant.

I can, of cause, provide for more rows than I need, but it leaves me with blanks in the drop down list, which I would prefer not to have.

Is there a way to work around it?

Thanks in advance.

Zagrijs

[mancubus]

why?
try with as many rows and columns as you want.

ps: since the array elements will be shown in msgbox try first, say, 10 X 5 table, then delete some cells' values to create 8 X 3 table, whatever, where top left cell is "A1"


Sub Test_Arr()

Dim arr()
Dim LastRow As Long, LastCol As Long
Dim iRow As Integer, iCol As Integer

With ActiveSheet
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
ReDim arr(1 To LastRow, 1 To LastCol)
arr = .Range(.Cells(1, 1), .Cells(LastRow, LastCol)).Value
End With
For iRow = LBound(arr, 1) To UBound(arr, 1)
For iCol = LBound(arr, 2) To UBound(arr, 2)
msg = msg & arr(iRow, iCol) & vbTab
Next iCol
msg = msg & vbCr
Next iRow

MsgBox msg

End Sub

[mdmackillop]

A couple of options
Private Sub UserForm_Initialize()

Dim Rng
'Listbox1 has 4 columns
With Sheets(1)
Set Rng = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 4)
ListBox1.List() = Rng.Value
End With

Dim arr()
'Listbox2 has 1 column
With Sheets(1)
Set Rng = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
ReDim arr(Rng.Rows.Count)
For i = 0 To UBound(arr) - 1
arr(i) = Rng(i + 1, 1)
Next

ListBox2.List() = arr

End With

End Sub

[mgm05267]

Simple :

Data in the column with say 10 rows, make the table as "format as table"

Name the data as Table1

Call the same in code.


Private Sub UserForm_Initialize()
Dim ws As Worksheet
Set ws = Worksheets("Index")
For Each cPart In ws.Range("Table1")
With Me.Combo1
.AddItem cPart.Value
.List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
End With
Next cPart
End sub

This code gets updated as and when the table1 gets updated... whether the table gets reduced to 6 or increased to 60...

Thanks & Regards,

MGM