Home > Validation > Drop Downs > Troubleshoot Data Validation TroubleshootingUse 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. |
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.
Here's the code for the macro – store it in a regular code module in your workbook. There are instructions on the Copy Code to a Workbook page.
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
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.
There are also troubleshooting suggestions on the Data Validation Tips and Troubleshooting page.
An extremely high count usually means that data validation rules have been applied to entire columns, instead of a small range of cells.
To find the data validation cells on the active sheet:
If entire columns are selected, remove the data validation rules from all of the rows that aren't being used.
If the data validation count was in the range that you expected, go to those sheets, and use the macros below, to list all the data validation rules on the active sheet.
There are two versions of the macro. They show the same details, but one puts the list on a new sheet in the active workbook, and one creates a text file with the results.
WARNING: If the data validation cell count is in the millions, DO NOT run the rule list macros. Excel will hang before it gets very far in the macro code. Delete the unnecessary data validation first.
After running the macros, if you don't spot any obvious problems in the lists of rules, there are more troubleshooting suggestions on the Data Validation Tips and Troubleshooting page.
The following macro creates a text file with a list of the active worksheet's data validation. In the screen shot below, the data validation locations and rules are listed in a text file.
At the end of the macro, it opens the folder where the text file was saved.
Here's the code for the macro – store it in a regular code module in your workbook. There are instructions on the Copy Code to a Workbook page.
WARNING: If the data validation cell count is in the millions, DO NOT run the following macro. Excel will hang before it gets very far in the macro code.
Thanks to J.E. McGimpsey for generously sharing his code.
Sub DataValDocText() ' www.contextures.com 'adapted from code posted by J.E. McGimpsey 2005-02-03 'https://www.mcgimpsey.com/excel/index.html Dim sVal(0 To 2) As Variant Dim rValidation As Range Dim rCell As Range Dim nFile As Long Dim sC As String Dim strDV As String Dim strPath As String strPath = Application.DefaultFilePath & "\" 'strPath = ThisWorkbook.Path & "\" sC = vbTab 'On Error Resume Next Set rValidation = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 If Not rValidation Is Nothing Then nFile = FreeFile Open strPath & " " & "test.txt" For Output As #nFile For Each rCell In rValidation With rCell.Validation sVal(0) = Choose(.Type + 1, "Input Only", _ "Whole Number", "Decimal", "List", "Date", _ "Time", "Text Length", "Custom") sVal(1) = .Formula1 sVal(2) = .Formula2 Select Case .Type Case xlValidateWholeNumber, xlValidateDecimal, _ xlValidateDate, xlValidateTime, xlValidateCustom Select Case .Operator Case xlAnd strDV = "Between" & sC & sVal(1) & sC & "And" & sC & sVal(2) Case xlNotBetween strDV = "Not Between" & sC & sVal(1) & sC & "And" & sC & sVal(2) Case xlEqual strDV = "Equal to" & sC & sVal(1) Case xlNotEqual strDV = "Not Equal to" & sC & sVal(1) Case xlGreater strDV = "Greater Than" & sC & sVal(1) Case xlLess strDV = "Less Than" & sC & sVal(1) Case xlGreaterEqual strDV = "Greater Than or Equal to" & sC & sVal(1) Case xlLessEqual strDV = "Less Than or Equal to" & sC & sVal(1) Case Else strDV = sVal(1) End Select Case Else strDV = sVal(1) End Select End With strDV = sC & sVal(0) & sC & strDV Print #nFile, rCell.Address(False, False) & strDV Erase sVal Next rCell Close #nFile MsgBox "Text file was saved in: " _ & vbCrLf _ & strPath Call Shell("explorer.exe" & " " _ & strPath, vbNormalFocus) End If End Sub
The following procedure creates a new worksheet in the active Excel file, with a list of the active worksheet's data validation rules and location.
Here's the code for the macro – store it in a regular code module in your workbook. There are instructions on the Copy Code to a Workbook page.
WARNING: If the data validation cell count is in the millions, DO NOT run the following macro. Excel will hang before it gets very far in the macro code.
Sub DataValDocSheet() 'adapted from code posted by J.E. McGimpsey 2005-02-03 'https://www.mcgimpsey.com/excel/index.html ' downloaded from www.contextures.com Dim sVal(0 To 2) As Variant Dim rValidation As Range Dim rCell As Range Dim ws As Worksheet Dim lRow As Long ' Dim nFile As Long Dim sC As String Dim strDV As String ' sC = vbTab On Error Resume Next Set rValidation = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 If Not rValidation Is Nothing Then Set ws = Worksheets.Add(Before:=Sheets(1)) With ws .Range("A1:C1").Value = Array("Cell", "DV Type", "Formula") lRow = 2 For Each rCell In rValidation With rCell.Validation sVal(0) = Choose(.Type + 1, "Input Only", _ "Whole Number", "Decimal", "List", "Date", _ "Time", "Text Length", "Custom") sVal(1) = .Formula1 sVal(2) = .Formula2 Select Case .Type Case xlValidateWholeNumber, xlValidateDecimal, _ xlValidateDate, xlValidateTime, xlValidateCustom Select Case .Operator Case xlAnd strDV = "Between" & sC & sVal(1) & sC & "And" & sC & sVal(2) Case xlNotBetween strDV = "Not Between" & sC & sVal(1) & sC & "And" & sC & sVal(2) Case xlEqual strDV = "Equal to" & sC & sVal(1) Case xlNotEqual strDV = "Not Equal to" & sC & sVal(1) Case xlGreater strDV = "Greater Than" & sC & sVal(1) Case xlLess strDV = "Less Than" & sC & sVal(1) Case xlGreaterEqual strDV = "Greater Than or Equal to" & sC & sVal(1) Case xlLessEqual strDV = "Less Than or Equal to" & sC & sVal(1) Case Else strDV = sVal(1) End Select Case Else strDV = "'" & sVal(1) End Select End With .Range(.Cells(lRow, 1), .Cells(lRow, 3)).Value _ = Array(rCell.Address(False, False), sVal(0), strDV) Erase sVal lRow = lRow + 1 Next rCell .Rows("1:1").Font.Bold = True .Columns("A:C").EntireColumn.AutoFit End With End If End Sub
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.
Data Validation Criteria Examples
Last updated: January 25, 2023 3:43 PM