I there a quick way to deselect all selections in all cells from dropdown lists?

Hello Forum,
I have a workbook with 2 sheets:
a) Data Validation
b) Part Number & QTY which has 6 drop down lists to select part numbers and QTY.
Goals:
1) A quick way to clear all selections by clicking “Deselect All” cell, instead of clicking individual cell to deselect it so it will go back to default.
2) Clicking “Deselect Row X” cell to clear all selections in that row so it will go back to default.
3) Is there a way to make my code shorter by nesting all the same conditions but different part number or columns (please see the VBA code)

Please see file attached.

Thank you so much for your help.

Thup_98

try this:

1. Select any cell with Data Validation & then Press the F5 key.
2. On the Go To Window, select "Special"
3. On the Go To Special Window, select "Data Validation" and "Same"
4. This should select all cells with similar Data Validation.
5. Type NA and then press [Ctrl] + [Enter] to apply it to all selected cells.

use record Macro to get a draft code, which can then be modified suitably.

Hi Jewelshama,
Thank you for quick direction but my auto fill qty=0 when the "NA" or QTY=1 when a Flasher # is selected stop working. This sheet is a small portion of a very large sheet (300 columns & hundred rows and each cell has its own data validation) that is the reason I would like to speed up the clearing process. I will try to modify the draft code. Do you have any idea for clearing the row & nesting?
Thank you
Thup_98

we can reset all desired cells with Worksheet_SelectionChange event; when the user clicks on the cell A24. Try the following code (also see attached workbook).

Please Login or Register  to view this content.

Hi Jewelshama or anyone able to offer any ideas on this thread?
Thank you so much for your help.
Thup_98

Hi Jewelshama,
Thank you for quick respond, but when I answer the Msg Box either Yes or No it will reset the data validation.
Thup_98

sorry mate, my oversight!
I have amended the code in post #7 above.

Hi Jewelshama,
It works perfectly.Do you have any idea how to make the code in my workbook_sheetchange shorter?

Please Login or Register  to view this content.

Thank you so much for your help!
Thup_98

Sure can. For e.g. you can replace these lines:

Please Login or Register  to view this content.

with these lines:

Please Login or Register  to view this content.

... you may amend/shorten other lines similarly.


However, I suggest you post this request in a separate 'New Thread'. That way, you are likely to receive response from more Forum members.

Also, trust your original request has been satisfactorily answered. Please take a moment to mark the thread as SOLVED. Here's how:

Method 1
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

Method 2
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

Thank you so much Jewelshama.
Thup_98