Home > Validation > Drop Downs > Troubleshoot 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.

Code for the Sheet Summary Macro

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

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.

• High Cell Count
• Normal Cell Count

There are also troubleshooting suggestions on the Data Validation Tips and Troubleshooting page.

Fix Sheets With High Cell Count

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:

1. On the Excel Ribbon, click the Home tab
2. At the far right, click Find & Select, then click Data Validation
3. All the data validation cells on the active sheet are selected

If entire columns are selected, remove the data validation rules from all of the rows that aren't being used.

1. Select the cells where you want to remove the data validation
2. On the Excel Ribbon, click the Data tab
3. In the Data Tools group, click Data Validation
4. If the selected cells have more than one type of data validation, a message will appear - "The selection contains more than one type of validation. Erase current settings and continue?"
   • Click OK to remove the data validation
   • Click OK to close the Data Validation dialog box
5. If the selected cells have only one type of data validation, the Data Validation dialog box will open
   • Click the Clear All button
   • Click OK to close the Data Validation dialog box
6. To confirm that all the unnecessary data validation has been cleared, use the Find & Select Data Validation command again.

Troubleshoot Sheets with Normal Cell Count

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.

• Data Validation List - Text File
• Data Validation List - New Sheet

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.

More Troubleshooting

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.

List All Data Validation: Text File

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.

Code for the Data Validation Text File Macro

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

List All Data Validation: Worksheet

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.

Code for the List All on Sheet Macro

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 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.

Excel Tutorials - Data Validation

Data Validation Basics

Create Dependent Lists

Data Validation Criteria Examples

Data Validation Custom Criteria Examples

Data Validation Tips