Data Validation - Actually finding the circled invalid data

[Neutralizer]

Hi,

Does anyone know of a way to automatically move the first/next cell that contains invalid data?

At the moment I'm having to scroll manually through it looking for the circles which is time consuming, because this sheet is pretty big...

By invalid data, i mean cells that are circled when using the 'Circle Invalid Data' option are data validation.

Cheers

 

[circledchicken]

Hi,

Here is one approach you can try:

Run the vba code provided here (there are complete instructions on how to do so): http://support.microsoft.com/kb/213773

This adds actual shapes to your worksheet around all the invalid cells. Now if you select one of the shapes and press tab you'll move to the next shape i.e. the next invalid cell.

Once your done you can easily remove the shapes by running the 'RemoveValidationCircles' macro.

 

[Neutralizer]

Excellent, thanks for that I'll give it a whirl.

 

[mikerickson]

Repeatedly calling this function will cycle through the Validation errors on the activeSheet

Sub NextValidationError()
    Dim vCell As Range, nextCell As Range, firstCell As Range
    On Error GoTo ErrorOut
    Set nextCell = Nothing: Set firstCell = Nothing
    
    For Each vCell In ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)
        With vCell
            If Not (.Validation.Value) Then
                If firstCell Is Nothing Then Set firstCell = vCell
                If nextCell Is Nothing Then Set nextCell = vCell
            End If
            If .Address = Selection.Address Then
                Set nextCell = Nothing
            End If
        End With
    Next vCell
    
    If nextCell Is Nothing Then Set nextCell = firstCell
    nextCell.Select
    
ErrorOut:
    Err.Clear
    On Error Goto 0
End Sub

 

[arhant01]

Thanks mikerickson !
Very helpful