Delete all text in dropdowns in a sheet

[Nick70]

Hi,

I have a worksheet which has some dropdowns which the analysts use when inserting client data.

I would like to have a macro which resets all dropdowns to zero so they are ready for new selection.

Normally one could do this by going to each dropdown cell and clicking on 'delete' button on keyboard.

This is a large worksheet so it would be ideal if one could run a macro that would delete all text in all cells which have a dropdown in that sheet (or alternatively all dropdowns in a specific range in that sheet for example Range(A1:H100)).

Sheet name is Sheet1.

Is there a way to do this?

Thanks,
Nic

 

[davesexcel]

Practice with this:
Select a range non validation and validation cells.
go to special cells (F5), and select Data Validation option.
After you click OK, cells with data validation should be selected.
Hit the delete key then.

Then practice with the macro recorder and perform the same actions to see what the code looks like.
You can clean up the code after to remove the selects.

 

[Nick70]

Works perfectly thanks!
N.