Error 400 when deleting strikethrough text

Question

I have Excel VBA code to delete strikethrough text:

Private Sub DelStrikethroughText()
    'Deletes strikethrough text in all selected cells
    Dim Cell        As Range
    For Each Cell In Selection
        DelStrikethroughs Cell
    Next Cell
End Sub

Private Sub DelStrikethroughs(Cell As Range)
    'deletes all strikethrough text in the Cell
    Dim NewText     As String
    Dim iCh         As Integer
    For iCh = 1 To Len(Cell)
        With Cell.Characters(iCh, 1)
            If .Font.Strikethrough = False Then
                NewText = NewText & .Text
            End If
        End With
    Next iCh
    Cell.Value = NewText
    Cell.Characters.Font.Strikethrough = False
End Sub

The code works on some cells.

The code fails at the last cell of the first row (so it depends on my selection). The macro is interrupted, just as it is supposed to change to row 2.

The cursor stops at NewText = NewText & .Text.

Why does Excel throw error 400?

Answer 1

Cell.Characters.Text does not work for cells containing formulas, numbers, dates etc. you can use Mid() function instead, however this may convert existing numbers to text:

Private Sub DelStrikethroughText()
    'Deletes strikethrough text in all selected cells
    Dim Cell        As Range
    For Each Cell In Selection
        If Cell.NumberFormat = "General" Then DelStrikethroughs Cell
    Next Cell
End Sub

Private Sub DelStrikethroughs(Cell As Range)
    'deletes all strikethrough text in the Cell
    Dim NewText     As String
    Dim iCh         As Integer
    For iCh = 1 To Len(Cell)
        With Cell.Characters(iCh, 1)
            If .Font.Strikethrough = False Then
                NewText = NewText & Mid(Cell, iCh, 1)
            End If
        End With
    Next iCh
    Cell.Value = NewText
    Cell.Characters.Font.Strikethrough = False
End Sub