I'm trying to develop a listbox that will change its list depending on another listbox that is selected. The number of items changes, so sometimes it will need to display 50 items and sometimes only 5. I want the code to recognize the last cell in the column with data entered and no more, so that the listbox doesn't have a long scroll bar for no reason.
Here is the code I have thus far, but it does not work.
The part in bold is the problem. 1) I do not want to define the range by a specific number of cells as I have with "DynamicCourseList", I want a code to only define the range with just enough for the filtered data. 2) the code as is doesn't work anyway. I keep getting an error for For i = 1 To Ubound(MyUniqueList2) line.Private Sub ListBox1_Click
Dim lSelected As Long
Dim r As Excel.Range
For lSelected = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lSelected) = True Then
Sheet3.Range("Q2") = ListBox1.List(lSelected)
ListBox1.Selected(lSelected) = False
Dim i As Long, MyUniqueList2 As Variant
.ColumnCount = 1
MyUniqueList2 = UniqueItemList(Range("DynamicCourseList"), True)
For i = 1 To UBound(MyUniqueList2)
.ListIndex = 0