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]

Thanks @Cubist, I just edited my post to say the formula in itself doesn't make sense because it is only ever going to put in 0 or "" where the 0 is what it converts "" to when you use a formula to bring it across.

PS: ?activecell.PrefixCharacter="'" returns False if the cell contains ""

Which formula are you referring to that does notr make sense?

 

[Alex Blakenburg]

I forgot to add that before running the code, all formulas are changed to thier calculated values.

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

 

[Alex Blakenburg]

Yes, that is why I wish to remove the apostrophe. In the cells where the formula had nothing to return eg. "", excel is entering an apostrophe

That shouldn't happen ! In File > More > Options > Advanced.
Right at the bottom under Lotus Compatibility, do you have transition navigation keys ticked ?
If so you need to uncheck that box

 

[Alex Blakenburg]

Which formula are you referring to that does notr make sense?

=IF('Sheet 2'!A2="",'Sheet 2'!A2,"")
You are saying go to sheet A2 and if it contains "" then return that value. So effecitively you are asking it to return "". The only thing is that returning "" using a formula converts it to return a 0.
And then you are saying if that A2 is not "" then put in ""

 

[Event2020]

Hi Alex

All Lotus options are unchecked.

 

[Alex Blakenburg]

Hi Alex

All Lotus options are unchecked.

Interesting. In that case I don't know how you are getting an apostrophe into your cells. If you are using a macro to convert it to value that might be doing it.
Try my code in post #12 it should fix your issue.

 

[Event2020]

=IF('Sheet 2'!A2="",'Sheet 2'!A2,"")
You are saying go to sheet A2 and if it contains "" then return that value. So effecitively you are asking it to return "". The only thing is that returning "" using a formula converts it to return a 0.
And then you are saying if that A2 is not "" then put in ""

Ah I see.

Sorry, my mistake i was not thinking clearly.

=IF(ISBLANK('Sheet 2'!A2),"",'Sheet 2'!A2)
If cell A2 in Sheet 2 has no data then do nothing but if it has data then copy that data into Cell A2 in the active sheet

Sorry for any confusion

 

[Event2020]

Interesting. In that case I don't know how you are getting an apostrophe into your cells. If you are using a macro to convert it to value that might be doing it.
Try my code in post #12 it should fix your issue.

Thank you alex for your continued assistence, I do appreaciate.

This formula is a very simplified version of a series of R1C1 formulas that are entered via VAB and the autofilled down the sheet.

I only used the simplified version as I did not want you guys to have to deal with the rest of the code but I can post the whole sub
if you wish.

I will also now try your code at post #12

 

[Alex Blakenburg]

For now just try the code in post #12 and see if it fixes your issue.
I have logged out for the night.

 

[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

Alex this works beautifully and the advanced filter now works as it should.

Thank you - you have solved a headache for me.

Genuinely grateful to you and the forum members who give up thier time to help with members questions.