How do I get rid of a blank drop down?

[rinxman]

I was modifying a file with a drop down list via Data Validation when the file started to always display the drop down arrow. It doesn't matter if the cell is selected or not. If I click on it, there are no options. It's just a blank box. I can insert rows and columns and the drop down arrow stays with the cell. But if I delete the row or column it moves to an adjacent cell. But when it moves instead of being outside the cell on the lower right, it is now inside the cell in the lower right corner.

I checked Data Validation and the only one is the one I was working on. How do I get rid of that arrow? Any ideas? I have closed completely out of Excel and then re-opened the file but it's still there.

I am using Excel 2007.

 

[ToniGo]

Get used to it!!! Happens all the time to me now!! In new spots in different spreadsheets.

What I do to remove it is drag the cell outside the active range. Then delete the whole row, then drag the formulas back over. It will depend on what formulas you have linking to it though that could be painful thgouh- if it's a named range then do the same, but before you delete then edit the named range to point to the replacement cell and copy any formulas etc back into there. Otherwise maybe do a find replace to point all formulas to the replacement cell before you delete.

Fun times...

 

[Meganraek]

It sounds like you guys are using a Table? If your are, it is a filter box and you have to click on the table, go to Design>Table Tools> Uncheck the "filter" box. This should get rid of those stupid blank drop down buttons.

Hope that works!

Megan

 

[ToniGo]

It sounds like you guys are using a Table? If your are, it is a filter box and you have to click on the table, go to Design>Table Tools> Uncheck the "filter" box. This should get rid of those stupid blank drop down buttons.

Hope that works!

Megan

Thanks for the idea Megan - but no, I'm not using a table. That is what the arrow looks like but it's not. I have tried clicking on the cell and checking if I can "clear filter" but it's not that. It's really random - just appears on it's own every now and again in different spots.

 

[ToniGo]

If anyone is interested in having a look I have saved a copy here:
https://www.dropbox.com/s/4v53hbtfdn3zlit/Blank Dropdown Example.xlsm?dl=0
It is in cell c30.

 

[Forrix]

Hi Folks,

This has driving me crazy for a while now, but i found a solution without deleting rows (since that would destroy my sheet).
Solution I found was here: I'm get blank drop down lists appearing with permanent arrows in - Microsoft Community

1) Open one of the sheets with the permanent drop downs appearing
2) Duplicate the sheet (right click the tab > 'Move or Copy' > select the desired sheet > check the 'Create a copy box'
3) Return to the original sheet with the issue. Notice that all of the strange drop down boxes are now outlined.
4) Hit Delete to remove them
5) Delete the duplicate sheet you created in Step 2 and continue as if nothing ever happened (hopefully...)

This is what worked for me. Hopefully this will help you guys aswell!

 

[ToniGo]

Thanks for the suggestion - however this didn't work for me. When I go back into the original sheet the box things are not selected. I did have a case the other day where I did find that they were selected and I was able to just hit delete to get rid of them, but I have not been able to select them any other time.

I have made some headway into working out when it happens (although still not the why!). I found that they appear when I run macro with a data validation cell selected. So when you run the macro, you cam see the arrow as per usual because the cell is selected. Then after running the macro, it has been lodged there permanently. I found that when I clicked off the cell first into a cell with no data validation, then it didn't happen. Not sure if this is an exclusive occurrence but I tested a few times moving on and off the cell and it kept happening.

I am quite sure though that I have other macros running with a data validation cell selected and it doesn't happen...so its definitely not one for one!

But hopefully that is helpful for anyone trying to avoid it.

 

[Rikitan]

Yes!! Sometimes you feel a need to register on website just to THANK someone. And this is it!
Thanks a lot, you saved me from going crazy.
I'm using both MS Excel 2010 and since I installed 365, it started to happen to me.
Making a copy of sheet and further steps worked fine to get rid of this bug.