Dynamic Listbox Missing Last Value

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,860
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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 ...

Rich (BB code):
'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:

Rich (BB code):
=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!
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,686
Try replacing the "-2" in your OFFSET -formula with "-1". That's what's removing the last value from your list.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Why not just use
Code:
     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

Well-known Member
Joined
Mar 23, 2004
Messages
3,860
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,128,113
Messages
5,628,777
Members
416,338
Latest member
Spartan5305

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top