Prevent Named Range Reference From Changing

[Ark68]

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 ...

=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?

 

[jim may]

Check out this article

https://www.cogniview.com/articles-resources/finally-stop-cell-references-from-changing-when-you-dont-want-them-to

 

[Ark68]

Thank you Jim!

I substituted my original with the INDIRECT function,

Named range 'employeenumber' refers to:

=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.

    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!

 

[jim may]

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?

=OFFSET(ROSTER!$A$2,0,0,COUNTA(ROSTER!$E:$E)

-2,1)

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

 

[jim may]

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)

 

[Ark68]

Thanks Jim for your support. This seems to be working wonderfully.