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

 

[Alex Blakenburg]

That code looks unreliable to me and you might want to elaborate on the nature of your data:
• CurrentRegion from A2 is very different to UsedRange. It also won't give you the true last row except in specific circumstances
• You are selecting Column K but not using it anywhere and doing the replacement in the whole UsedRange is this your intent ?
Do you want to replace in Column K only or the whole used Range. ?
What is the nature of the data, are there formulas in there ? Are there a combination of numbers and text ? If numbers are they intended to be stored as text or numbers ?

Depending on your answer one of these might work ?

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

With ActiveSheet.UsedRange
    .Value = .Value
End With

 

[JoeMo]

What is the formula in column K?

 

[Event2020]

That code looks unreliable to me and you might want to elaborate on the nature of your data:
• CurrentRegion from A2 is very different to UsedRange. It also won't give you the true last row except in specific circumstances
• You are selecting Column K but not using it anywhere and doing the replacement in the whole UsedRange is this your intent ?
Do you want to replace in Column K only or the whole used Range. ?
What is the nature of the data, are there formulas in there ? Are there a combination of numbers and text ? If numbers are they intended to be stored as text or numbers ?

Depending on your answer one of these might work ?

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

With ActiveSheet.UsedRange
    .Value = .Value
End With

Hi Alex

Yes, it is not the best code which is why I am asking in here.

Thank you for your questions which I will now answer.

Q. You are selecting Column K but not using it anywhere and doing the replacement in the whole UsedRange is this your intent ?

Yes it is my intent to do the replacement on the whole of Column K from K2 down to the last used row.

Q Do you want to replace in Column K only or the whole used Range. ?
Only Column K

Q What is the nature of the data
Where cells in Column K has data, it will be text

Q Are there formulas in there ?
A formula was used to pull data into Column K?
Starting with A2 on Sheet 2 and downwards

 =IF('Sheet 2'!A2="",'Sheet 2'!A2,"")

Q Are there a combination of numbers and text ?
No. Only text


Q If numbers are they intended to be stored as text or numbers ?
See answer for previous question.

 

[Event2020]

What is the formula in column K?

=IF('Sheet 2'!A2="",'Sheet 2'!A2,"")

 

[Alex Blakenburg]

I don't understand, with that formula I can't get a single quote "'" into column K.
How are you seeing that there is a single quote in column K ?

Actually the formula doesn't make sense if A2 is "" then A2 otherwise put "".
That would put empty in it either way except that putting in A2 when its "" would result in 0

 

[Cubist]

I don't understand, with that formula I can't get a single quote "'" into column K.
How are you seeing that there is a single quote in column K ?

I believe the OP is confused between "" vs. when you enter ' to indicate text string without any additional text. The latter appears blank but it's not blank.

 

[Alex Blakenburg]

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 ""

 

[Event2020]

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

 

[Event2020]

I believe the OP is confused between "" vs. when you enter ' to indicate text string without any additional text. The latter appears blank but it's not blank.

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