Excel Data Validation Blind Spot

Macro to fix it

One of the problems with Excel’s Data Validation is that it is possible to have an invalid entry in a data validation cell. This can be caused by Paste Special Values or linked drop downs that don’t update if an earlier drop down is changed. To easily identify invalid cells you can use a macro.

Just be clear Excel has a built-in feature to show invalid cells BUT it has two major limitations.

The first is it doesn’t tell you if there are any invalid cells. If there are invalid cells, it doesn’t tell you how many there are, or where they are.

The second limitation is that it circles invalid cells. This means you must examine each data validation cell to see if it is circled across all your sheets.

Macro solution

This macro doesn’t need to be in the file involved. It can stay in your Personal Macro Workbook.

I assume most data validation cells are valid. This macro displays a message box with the details of each invalid data validation cell. If you had a lot of invalid cells you could create a spreadsheet report listing them.

The Code

Below is the macro code. The green rows are comments and  explain the code.

Sub FindInvalidCells()
'displays a msgbox with detail of each invalid data validation cell
 
    Dim ws As Worksheet
    Dim rngData As Range
    Dim rngCell As Range
    Dim lCounter As Long
 
    'loop through all the sheets
    For Each ws In Worksheets
 
        'reset the range
        Set rngData = Nothing
 
        On Error Resume Next
        'this If statement generates an error if there are no data validation cells
        ' in the sheet. So it will do nothing if an error is found by moving to
        '  the next line. If no error is encountered the range is
        '   captured in the rngData variable
        If ws.Cells.SpecialCells(xlCellTypeAllValidation).Rows.count = 0 Then
            'do nothing
        Else
            Set rngData = ws.Cells.SpecialCells(xlCellTypeAllValidation)
        End If
 
        ' Loop through each data validation cell and identify each invalid cell
        '  first check the range is not Nothing
        If Not (rngData Is Nothing) Then
            For Each rngCell In rngData
                If Not rngCell.Validation.Value Then
                    MsgBox "Validation error found in " & ws.Name & _
                        " and cell " & rngCell.Address, , vbCritical + vbOKOnly, _
                        "INVALID CELL FOUND"
                    lCounter = lCounter + 1
                End If
            Next rngCell
        Else
            'do nothing if the range is nothing
        End If
 
    Next ws
 
    If lCounter > 0 Then
        MsgBox lCounter & " invalid cell(s) found.", vbCritical + vbOKOnly, _
            "INVALID CELL(S) FOUND"
    Else
        MsgBox "All data validation cells are valid.", vbInformation + vbOKOnly, _
            "No errors found"
    End If
 
    'clear all the range variables
    Set rngData = Nothing
    Set rngCell = Nothing
    Set ws = Nothing
 
End Sub

This examines each sheet in the file and each data validation cell in each sheet. If a sheet doesn’t have a data validation cell the SpecialCells command used would generate an error. To get around that I have used the On Error Resume Next command. This command should not be over-used.

In this case it handles a possible error. This code doesn’t affect or change the spreadsheet so error handling isn’t as important as with other routines.

Using macros

If you are unsure how to use macros see this post.

Related Posts

Posted on
Categories: Data, Excel Blog, Macros

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on “Excel Data Validation Blind Spot”

  1. Another approach your readers could consider is to accumulate the cell addresses for the validation errors in a text string and then output that list in a MessageBox at the end of the macro. Here is one way such an approach could be coded…

    Sub FindInvalidCells()
    Dim Cnt As Long, Errs As String, Cell As Range, WS As Worksheet
    For Each WS In Worksheets
    On Error GoTo NoValidationOnSheet
    For Each Cell In WS.Cells.SpecialCells(xlCellTypeAllValidation)
    If Not Cell.Validation.Value Then
    Cnt = Cnt + 1
    Errs = Errs & “, ‘” & WS.Name & “‘!” & Cell.Address(0, 0)
    End If
    Next
    Continue:
    Next
    MsgBox “There were ” & Cnt & ” Validation errors:” & vbLf & Mid(Errs, 3)
    Exit Sub
    NoValidationOnSheet:
    Resume Continue
    End Sub

    Reply

    • Thanks for sharing Rick
      Interesting use of the Continue label
      I considered a single message box and also a report sheet listing all the cells but my assumption was there shouldn’t be a lot of invalid cells.
      If there was then yes listing then in one go is a better way to go.

      Thanks again.

      Reply