Waarde: #VERW! uit cellen verwijderen

Sub FindAllRefErrors()
    
    Dim rAllRefErrors As Range
    
    Set rAllRefErrors = AllRefErrors(Columns("C:C"))
    
    If Not rAllRefErrors Is Nothing Then
        rAllRefErrors.Select
    Else
        MsgBox "No REF errors found", vbInformation, "Output"
    End If

End Sub

Function AllRefErrors(r As Range) As Range
    
    Dim rTemp As Range
    Dim rTemp2 As Range
    Dim rng As Range
    Dim CellValue As Variant
    
    On Error Resume Next
    Set rTemp = r.SpecialCells(xlCellTypeFormulas, xlErrors)
    On Error GoTo 0
    
    If rTemp Is Nothing Then
        
        Set AllRefErrors = Nothing
        Exit Function
    
    Else
        
        For Each rng In rTemp
            
            CellValue = rng.Value
            
            If CellValue = CVErr(xlErrRef) Then
            
                If rTemp2 Is Nothing Then
                
                    Set rTemp2 = rng
                
                Else
                
                    Set rTemp2 = Application.Union(rTemp2, rng)
                    
                End If
            
            End If
            
        Next
        
    End If
    
    Set AllRefErrors = rTemp2
    
End Function

Sub DeleteFormula_Ref()

    Dim oCell As Range
    Dim cRng As Range

    Sheets("Sheet3").Activate 'pas aan

    On Error Resume Next
CheckAgain:
    Set cRng = Selection.SpecialCells(xlFormulas, 16)
    If cRng Is Nothing Then Exit Sub
    For Each oCell In cRng
        SearchF (oCell.Formula)
        oCell.Formula = Str
    Next
    Set cRng = Nothing
    GoTo CheckAgain
End Sub

Function SearchF(cell)

    Dim arr
    Dim l As Long
    
    Const sRef As String = "#REF!"
    
    arr = Split(cell, "+")
    
    For l = LBound(arr, 1) To UBound(arr, 1)
        If InStr(arr(l), sRef) Then arr(l) = ""
    Next
    
    Str = Join(arr, "+")
    
    Do While InStr(Str, "++") > 0
        Str = Application.WorksheetFunction.Substitute(Str, "++", "+", 1)
    Loop
    
    SearchF = Str
    
End Function