I have this problem. Basically, program says it can't use the union range I've created for the .list property of my listbox. I'd like to trigger it when the userform initializes. The problem lies in the last line of code. Although when I try to .select UnionRange it won't work either. But if I do it without looping through the sheets it works ok.
Private Sub UserForm_Initialize()
Dim sh As Worksheet
Dim i As Long, RowNo As Long
Dim UnionRange As Range
'Loops through all sheets in workbook
For Each sh In Worksheets
'Stops looping when it reaches "LISTS" sheet
If sh.Name = "LISTS" Then
Exit For
End If
sh.Activate
RowNo = Range("e6").End(xlDown).Row
For i = RowNo To 1 Step -1
If Range("K" & i) = "TBD" Then
If UnionRange Is Nothing Then
Set UnionRange = Range("k" & i)
Else
Set UnionRange = Union(UnionRange, Range("k" & i))
End If
End If
Next
Next
'Error is next, should populate listbox list
lbTBDNAV.List = UnionRange.Value
End Sub
TBD. Did you want to combine cells in another column or is TBD part of the strings? Maybe you want to look inEand useK. Please clarify. BTW, you cannot combine ranges from different worksheets. You are calculating the last row fromE6yet you are looping until row1. Instead of activating the worksheet, qualify the ranges withsh.Range(...).