Select method of Range class failed

[akk]

Hello,

I have this code in a form, which is designed to populate a combo box with all the values of non-empty rows in a given column.

The problem with this is that a "Runtime Error: 1004
Select method of Range class failed" occurs

' set variables
Dim wsSheet As Worksheet
Dim rngNext As Range
Dim myRange As Range
Set wsSheet = Worksheets("Staff")
With wsSheet
    Set rngNext = .Range("A65536").End(xlUp).Offset(1, 0)
End With
rngNext.Select
Set myRange = Range("a2", rngNext)
 
'populate Group combobox list
With ComboStaff
    Selection.End(xlUp).Select
    For Each rngNext In myRange
        If rngNext = "" Then
            Selection.End(xlUp).Select
        End If
            If rngNext <> "" Then .AddItem rngNext
        Next rngNext
End With

Could someone please point me in the right direction, as to how to fix this problem.

Thanks

 

[RoryA]

You can't select a cell on a sheet unless that sheet is active, but you also don't need to select anything to do this:

' set variables
Dim wsSheet As Worksheet
Dim rngCell As Range
Dim myRange As Range
Set wsSheet = Worksheets("Staff")
With wsSheet
    Set myRange = .Range("a2", .Range("A65536").End(xlUp))

End With
'populate Group combobox list
With ComboStaff
    For Each rngCell In myRange
        If Len(rngcell.Value) > 0 Then .AddItem rngCell.Value
    Next rngCell
End With

 

[akk]

Thank you, that seems a bit simpler