VBA to remove Apostrophes works but also gives Run-time error 13. Unable to work out why?

[Event2020]

Windows 10
Excel 2019


In Column K, the cells are populated by a formula.

In some cells the formula will have nothing to return ("") and Excel inserts a (') Apostrophe which I need to remove but only for the cells where the formula has returned nothing.


I have the code below that selects Cell K2 down to the last used row.

Then for any cell that is blank it removes the (') Apostrophe.

This works and all of the Apostrophes are remove but VBA does give a Run-time error '13' Type mismatch on the line of code

If r.PrefixCharacter = "'" And Len(r.Value) = 0 Then

I would like to ask 2 questions if I may.

1. What is causing the error and how may it be fixed?
2. Is there a better (and faster) way to do this task?

This code will be inserted at the beginning of another sub, which is an advanced filter, to prepare Column K for the filter as the Apostrophes are causing the filter not to work correctly.
Once the Apostrophes are removed the filter works perfectly.

Sub ClearApostraphie()
Dim sht As Worksheet
Dim LastRow As Long
Dim r As Range

Set sht = ActiveSheet

LastRow = sht.Range("K2").CurrentRegion.Rows.Count

sht.Range("K2", sht.Range("K" & LastRow)).Select

For Each r In ActiveSheet.UsedRange
        If r.PrefixCharacter = "'" And Len(r.Value) = 0 Then
            r.Clear
        End If
    Next r
End Sub

 

[Event2020]

Based on that and your previous responses change your code to this:

Sub ClearApostraphie()
    Dim sht As Worksheet
    Dim LastRow As Long
   
    Set sht = ActiveSheet
   
    LastRow = sht.Range("K" & Rows.Count).End(xlUp).Row
   
    With sht.Range("K2:K" & LastRow)
        .Value = .Value
    End With

End Sub

As an aside and in case it helps anyone else reading this thread I have been able to do away with the seperate SUB that was changing the formulas in Column K to thier calculated values by add a single line of code in to yours as yours was already selecting the range that had data, or where the formula returned data.

I added

.Formula = .Value

inside the "With" like so

With sht.Range("K2:K" & LastRow)
    .Formula = .Value
        .Value = .Value


    End With

Thank you again Alex.

 

[rlv01]

I'm not clear on what your added line of code does. I would not expect any difference between this

    With sht.Range("K2:K" & LastRow)
        .Formula = .Value
        .Value = .Value
    End With

and just this

    With sht.Range("K2:K" & LastRow)
        .Value = .Value
    End With

 

[Event2020]

I'm not clear on what your added line of code does. I would not expect any difference between this

    With sht.Range("K2:K" & LastRow)
        .Formula = .Value
        .Value = .Value
    End With

and just this

    With sht.Range("K2:K" & LastRow)
        .Value = .Value
    End With

Your right - a rooky error on my part I'm afraid.