Code:
' - - - - - - - - - - - - - - - - - - - - - - - - - - -
' This will get info of certain list in an excel range
' The basis for look up is the cell one row above the first list entry
' (this cell is usually reserved for title of the first column)
Function xlrangeListCount(SheetName As String, RangeName As String, Optional OffsetC = 0) As Long
Dim thisCount As Long
With Sheets(SheetName).Range(RangeName)
thisCount = 0
Do
If Not (IsEmpty(.Offset(thisCount + 1, 0))) Then
thisCount = thisCount + 1
Else
Exit Do
End If
Loop
End With
xlrangeListCount = thisCount
End Function
Function xlrangeListItem(SheetName As String, RangeName As String, OffsetRow As Long, Optional OffsetCol = 0)
xlrangeListItem = Sheets(SheetName).Range(RangeName).Offset(OffsetRow, OffsetCol).Value
End Function
Now, here is an example of using those 2 function to create a dynamic array.
Code:
Sub GetRangeList()
Dim MyList()
Dim i As Long
Dim ListCount As Long
ListCount = xlrangeListCount("Sheet1", "A1")
ReDim MyList(1 To ListCount, 1 To 3)
For i = 1 To ListCount
MyList(i, 1) = xlrangeListItem("Sheet1", "A1", i)
MyList(i, 2) = xlrangeListItem("Sheet1", "A1", i, 1)
MyList(i, 3) = xlrangeListItem("Sheet1", "A1", i, 2)
Next
End Sub
Take note that this code assumes your list have headers(or titles) and the "A1" refered to is the first header in the list.
I assume you can put it (MyList()) in a combobox.