Prevent Named Range Reference From Changing

Ark68

Well-known Member
I have two named ranges I use as sources for their respective combobox lists.
Since the ranges are dynamic, I use a formula as reference ...

Rich (BB code):
=OFFSET(ROSTER!$A$2,0,0,COUNTA(ROSTER!$E:$E)-2,1)
WHat is happening though, from within my VBA application, the worksheet will many times get altered with the deletion and/or addition of rows, and the first parameter of OFFSET will change. When it changes, my list source is off.

For example,if I delete a row, the $A$2 will change to $A$3. If I delete another row, it changes to $A$4. When this happens my list source misses the first two values of the column, and makes up for the difference with empty spaces.

How can I prevent the involuntary changing of this parameter?
 

Ark68

Well-known Member
Thank you Jim!

I substituted my original with the INDIRECT function,

Named range 'employeenumber' refers to:
Rich (BB code):
=OFFSET(INDIRECT(ROSTER!$A$2),0,0,COUNTA(ROSTER!$A:$A)-2,1)
but now I get an error ("Method 'Range' of object '_Worksheet' failed" with this line in my code.

Rich (BB code):
    With ws_rstr
        lrow = (.Cells(.Rows.Count, "A").End(xlUp).Row) - 1
        .Range("A2:U" & lrow).Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlNo
     End With
    Me.empl_no.Clear
    For Each cl In ws_rstr.Range("employeenumber")
        If cl.Value <> 0 Then
            Me.empl_no.AddItem cl.Value
        End If
    Next cl
    Me.empl_no.AddItem "NEW"
Any help would be greatly appreciated!
 
Last edited:

jim may

Well-known Member
Just curious but why, In the 2 lines of code you have shown are you in the first using the "-2" and in the second using the "-1"?
In each case you are EXCLUDING the last row in your range. Is that what you want?

Rich (BB code):
=OFFSET(ROSTER!$A$2,0,0,COUNTA(ROSTER!$E:$E)
Rich (BB code):
-2,1)

lrow = (.Cells(.Rows.Count, "A").End(xlUp).Row) - 1lrow = (.Cells(.Rows.Count, "A").End(xlUp).Row) - 1
 

jim may

Well-known Member
Try this... (in RefersTo Box for "employeenumber")
Should Prevent your reference to $A$2 from EVER CHANGING!!

=OFFSET(INDIRECT("'ROSTER'!$A$2"),0,0,COUNTA(INDIRECT("'ROSTER'!$E:$E"))-1,1)
 

Some videos you may like

This Week's Hot Topics

Top