Determine if cell contains data validation

Question

I am writing a VBA code that goes through a range of cells checking if each cell has data validation (drop down menu) and if not assign one to it from a list on another sheet.

I currently have trouble with the line that checks if the current cell already has data validation. I get error 1004 "no cells were found".

Sub datavalidation()

    Dim nlp As Range
    Dim lrds As Long
    Dim wp As Double
    Dim ddrange As Range

    Sheets("DataSheet").Select

        lrds = ActiveSheet.Range("A1").Offset(ActiveSheet.rows.Count - 1, 0).End(xlUp).Row

        Set nlp = Range("I3:I" & lrds)

        For Each cell In nlp

    'error on following line

            If cell.SpecialCells(xlCellTypeSameValidation).Cells.Count < 1 Then
                wp = cell.Offset(0, -8).Value

                Set ddrange = ddrangefunc(wp)

            End If

        Next

End Sub

Any ideas? Thank you

Answer 1

I know this question is old, but since it comes up when Googling "excel vba check if cell has validation", I figured I would add my grain of salt.

If the Range object on which you call SpecialCells represents only a single cell, the entire sheet will be scanned to find matches. If you have a very large amount of data, the methods provided in previous answers may become a bit slow.

Hence, here is a more efficient way to check if a single cell has validation:

Function HasValidation(cell As Range) As Boolean
    Dim t: t = Null

    On Error Resume Next
    t = cell.Validation.Type
    On Error GoTo 0

    HasValidation = Not IsNull(t)
End Function

Answer 2

Looking for a way to handle this avoiding the error resume next. This is the way I implemented it:

Option Explicit
' https://stackoverflow.com/questions/18642930/determine-if-cell-contains-data-validation
' Use this if you want to omit doing something to the cell added: http://dailydoseofexcel.com/archives/2007/08/17/two-new-range-functions-union-and-subtract/
Sub ValidationCells()

    Dim theSheet As Worksheet
    Dim lastCell As Range
    Dim validationRange As Range
    Dim validationCell As Range
    
    Application.EnableEvents = False ' optional
    
    Set theSheet = ThisWorkbook.Worksheets(1)
    
    theSheet.Unprotect ' optional
    
    ' Add a cell with a value and some validation to bypass specialcells error
    Set lastCell = theSheet.Cells(1, theSheet.Cells.Columns.Count)
    With lastCell
        .Value2 = 1
        .Validation.Add xlValidateWholeNumber, xlValidAlertInformation, xlEqual, "1"
    End With
    
    ' If usedrange is greater than 1 (as we added a single cell previously)
    If theSheet.UsedRange.Rows.Count > 1 Or theSheet.UsedRange.Columns.Count > 1 Then
    
        Set validationRange = theSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
        
        MsgBox validationRange.Address
        
        For Each validationCell In validationRange
            If validationCell.Address <> lastCell.Address Then
                MsgBox validationCell.Address
            End If
        Next validationCell
        
    End If
    
    lastCell.Clear
    
    Set validationRange = Nothing
    Set lastCell = Nothing
    
    theSheet.Protect ' optional
    
    Application.EnableEvents = True ' optional
    

End Sub