Dynamic Listbox Missing Last Value

[Ark68]

I have a roster of employee names in column E of my worksheet. I use this data as a source for a listbox on my userform. The roster is dynamic with names coming and going, so I am unable to use a static range as the listbox source.

I have gone with the approach ...

'create employee name list
     'sort
     With ws_rstr
        lrow = (.Cells(.Rows.Count, "E").End(xlUp).Row) ' - 1
        .Range("A2:U" & lrow).Sort Key1:=.Range("E2"), Order1:=xlAscending, Header:=xlNo
     End With
     'create list
     Me.empl_name.Clear
     For Each cl In ws_rstr.Range("employeename")
        Me.empl_name.AddItem cl.Value
     Next cl
     Me.empl_name.AddItem "NEW"  ' this appends NEW at the end of this created list

The named range "employeename" holds the dynamic column of names from column E. If is defined as:

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

For the most part, this works, however, the last row of the column is excluded. I don't know enough about the named range reference formula to know how, if at all, it's contributing to the problem. Any guidance would be greatly appreciated!

 

[Misca]

Try replacing the "-2" in your OFFSET -formula with "-1". That's what's removing the last value from your list.

 

[Fluff]

Why not just use

     With ws_rstr
        lRow = (.Cells(.Rows.Count, "E").End(xlUp).Row) ' - 1
        .Range("A2:U" & lRow).Sort Key1:=.Range("E2"), Order1:=xlAscending, Header:=xlNo
        .Range("E" & lRow + 1) = "NEW"
        Me.empl_name.List = .Range("E2:E" & lRow + 1).Value
        .Range("E" & lRow + 1).ClearContents
     End With

No need for named ranges

 

[Ark68]

Hi Misca, that was the answer. Your reply has helped me understand that formular much better now.

Fluff, brilliant as usual! Typical Excel, more than one way to do a job. I can actually understand the logic behind your code!

 

[Fluff]

Glad we could help & thanks for the feedback