Drop Down Box & Name Manager

[cart0250]

Hi all,

I have name set in name manager pointing to Col B, Sheet 1 as =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet!$B:$B),1)

User is presented with drop down menus in various cells in a different work sheet pertaining to this named range. Once they select an item from the drop down, the item is removed from Col B Sheet 1 and placed into Col C Sheet 1 (via VBA). Meaning that item is no longer available to be selected from the drop down. This works correctly as user is only allowed to select each item once. However, the drop down now contains a blank space corresponding to the item that has been removed. Once a large number of items have been selected and removed, the number of blank spaces becomes a nuisance.

Question: is there a way to modify the above formula to eliminate these blanks from the drop downs, i.e., to only show whatever non-blank cells remain in Col B Sheet 1?

Appreciate the help!

 

[Micron]

Methinks you need your vba code to also delete the cell that you remove the value from, then shift up. Without seeing that code, all I can suggest is to incorporate
.Delete Shift:=xlUp
on the same range you delete from.

 

[Peter_SSs]

1. I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

User is presented with drop down menus in various cells in a different work sheet

2. Are these "various cells" in a particular column/row range or scattered about? How do we know where they are?

the item is removed from Col B Sheet 1 and placed into Col C Sheet 1 (via VBA)

3. Could we see the vba?