Populating Userform Listbox with Dynamic Range

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.

Private Sub ListBox1_Click

Worksheets("Fed_feedback").Range("$Q$2").ClearContents
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
End If
Next
Call ProfSort

Dim i As Long, MyUniqueList2 As Variant
With UserForm1.ListBox2
.ColumnCount = 1
.Clear
MyUniqueList2 = UniqueItemList(Range("DynamicCourseList"), True)
For i = 1 To UBound(MyUniqueList2)
.AddItem MyUniqueList2(i)
Next i
.ListIndex = 0
End With

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.

Thoughts??

Originally Posted by JosephP

I reckon you need to provide a workbook or at least all the code you are calling in that snippet

Are you referring to the Call ProfSort? That is just a macro to do the advanced filtering in the first listbox. It's unrelated to what I'm trying to do in the second....I think. But here is the module for that

Sub ProfSort()
Range("$A$1:$P$2717").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("$Q$1:$R$2"), CopyToRange:=Range("$S$1:$AH$2717"), Unique:=False
End Sub

I guess to give a better understanding of what I'm trying to do overall: the first listbox is filtering by professor name upon click. Those filtering results include a new course listing that I want to populate the second list box with. I want to input a code that does so, but is dynamic to the amount of items resulting from the first filtering.

Does that make things a little clearer, or am I still providing the wrong info? (bear with me, I'm a newbie, but I'm trying!)