Data Validation Troubleshooting

Use these macro to start your data validation troubleshooting. Get a list of all data validation on the active sheet -- the list can be created on a new worksheet, or in a text file. Also, get a list of all sheets, to see a count of data validation cells -- helpful for troubleshooting.

List All Sheets with DV Info

To start your data validation troubleshooting, use this first macro to see what is in the active workbook. This macro adds a new sheet in the workbook, and lists all the sheets, with a summary of the information on those sheets, including the count of data validation cells.

This is helpful when troubleshooting a workbook -- sometimes entire columns have data validation added, and that can cause Excel to slow down, or to crash. If you see a high number of data validation cells listed, you'll know where to focus your troubleshooting. You'll also see which sheets have NO data validation, so you can ignore those for now.

NOTE: There is a Sheet Info command in my Excel Tools add-in, and it creates a list of sheets with several columns of details.

Next Steps for Troubleshooting

After you run the sheet summary macro, you'll know which sheets have data validation rules, and how many cells have those rules. You can go to those sheets to dig in deeper, based on the data validation cell counts.

Automatic Lists with Excel Tools

If you have a copy of my Excel Tools add-in, it has a command to create a list of all the data validation on the active sheet.

After you click the Data Validation command, a message appears, asking if you want to list the rules for the selected cells only. If you click No, the list will show the rules for the entire active worksheet.

Worksheets Summary List

The Excel Tools add-in also has commands to create lists of sheet contents, such as the summary list shown below. This list shows the data validation cell counts, as well as formula counts, used range address, tab colour, and other details.

Download the Sample File

Download the sample Data Validation
Documenter file. The zipped file is in xlsm format, and contains macros. When you open the workbook, be sure to enable macros, if you want to test the Data Validation Documentation macros.