How many data validation rules do you have in the giant Excel workbook that you use every morning? If you inherited that file from somebody else, you might not have any idea what's on some of the sheets. Your workbook could be slowing down, or maybe it's even crashing, and you're not sure why. Use these macros for data validation troubleshooting, and see if you need to clean things up.
Too Much Data Validation
Recently, someone sent me a workbook that was crashing frequently. They were trying to use my Data Entry PopUp List kit, and thought that might be the problem.
The workbook was small – they had removed most of the data, for privacy reasons. But, when I tried to save the file, it crashed.
So, I opened the file again, and ran a macro to list all the sheets, with details of what was on each sheet. Can you spot the problem?
That's right – over 5 million cells that have data validation! Instead of adding the rules to just the columns in an Excel table, entire worksheet columns had the rules.
A little data validation can be a good thing – it controls what people can enter on the worksheet. But this much DV was definitely not a good thing!
So, I removed all the unnecessary data validation rules, and the workbook stopped crashing.
Data Validation Troubleshooting
There is a macro in the next section, and you can use it for your data validation troubleshooting.
The macro adds a new sheet in the active workbook. On that sheet, it lists all the other sheets, with a summary of the information on each sheet:
- Sheet Name
- Used Range Address
- Count of cells in Used Range
- Count of data validation cells
Here's a screen shot of the list from one of my sample files. I put data validation in the full column on the DV03 sheet, and you can see the high count.
Code for the List All Sheet Details Macro
Here's the code for the macro that creates the list of sheet details – store it in a regular code module in your workbook. There are instructions on my Contextures website.
Sub DataValSummary() Dim ws As Worksheet Dim lCount As Long Dim wsTemp As Worksheet Dim rngF As Range Dim lFields As Long Dim lTab As Long Dim rngDV As Range Dim vDV As Variant Dim strNA As String Dim strSh As String Application.EnableEvents = False Application.ScreenUpdating = False On Error Resume Next Set wsTemp = Worksheets.Add(Before:=Sheets(1)) lCount = 2 lFields = 5 'not tab color strNA = " --" With wsTemp .Range(.Cells(1, 1), .Cells(1, lFields)).Value _ = Array( _ "Order", _ "Sheet Name", _ "Used Range", _ "Range Cells", _ "DV Cells") End With For Each ws In ActiveWorkbook.Worksheets If ws.Name <> wsTemp.Name Then If ws.ProtectContents = True Then vDV = strNA strSh = strNA Else Set rngDV = Nothing vDV = 0 Set rngDV = ws.Cells.SpecialCells(xlCellTypeAllValidation) If Not rngDV Is Nothing Then vDV = rngDV.Cells.Count End If End If With wsTemp .Range(.Cells(lCount, 1), .Cells(lCount, lFields)).Value _ = Array( _ ws.Index, _ ws.Name, _ ws.UsedRange.Address, _ ws.UsedRange.Cells.Count, _ vDV) 'add hyperlink to sheet name in column B .Hyperlinks.Add _ Anchor:=.Cells(lCount, 2), _ Address:="", _ SubAddress:="'" & ws.Name & "'!A1", _ ScreenTip:=ws.Name, _ TextToDisplay:=ws.Name lCount = lCount + 1 End With End If Next ws With wsTemp With .Range(.Cells(1, 1), .Cells(1, lFields + 2)) .EntireColumn.AutoFit .AutoFilter End With .Rows(1).Font.Bold = True End With Application.EnableEvents = True Application.ScreenUpdating = True End Sub
List All Data Validation Rules
After you run that macro, you might spot a problem right away, and be able to fix it. If not, there are more suggestions and tools on the Data Validation Troubleshooting page on my Contextures website.
For example, use the macros from that page, to list all data validation rules on the active worksheet, so you can see the cell address, the data validation type, and the formulas.
Don't run those macro on a sheet with millions of data validation cells though – get rid of any unnecessary DV cells first. Otherwise, Excel might melt into a puddle.
New Sheet with List
One macro adds a new sheet to the workbook, and lists the details there.
Text File with List
The other macro lists the details in a text file, created in your default file save location.
Download the Workbook
To download the data validation troubleshooting workbook, go to the Data Validation Troubleshooting page on my Contextures site.
The zipped file is in xlsm format, and contains all three data validation troubleshooting macros. When you open the workbook, be sure to enable macros, if you want to create the lists.
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. (It has lots of other troubleshooting tools too!)
When you click that 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.
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.