Select method of Range class failed

akk

New Member
Joined
Mar 19, 2009
Messages
36
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

Code:
' 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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:
Code:
' 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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,976
Messages
5,526,009
Members
409,675
Latest member
audeser

This Week's Hot Topics

Top