VBA to Clear texts with Strikethroughs, Red fonts and semicolons in cells (selection)

[alishern]

Hi,

I have a large excel file that I receive from the users with comments. The comments include texts with strikethroughs in red fonts that need to be removed - basically, the text with strikethroughs (and sometimes in red fonts) are obsolete and need to be removed. I need to be able to remove the texts with strikethroughs inside each cell in a selected range. Here are a few examples:

Example 1 - the result does not need to retain the original formatting (blue font in this case)
View attachment 99109

Example 2

Dug up a few codes (see below), but it throws this error. As usual, thank you in advance.

Error:

Sub DelStrikethroughText()
  
   Application.ScreenUpdating = False
    
   'Deletes strikethrough text in all selected cells
   Dim Cell    As Range
  
   For Each Cell In Selection
      DelStrikethroughs Cell
   Next Cell
  
   'remove removeSemiCol texts
   Selection.replace What:=";", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

        
   Application.ScreenUpdating = True
  
End Sub

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

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[alishern]

You're welcome, glad to help & thanks for the feedback.

Akuini,

This case is considered as resolved, but if I were to expand the range from currently 2 columns to 5 columns, where in the code do i make the edit?

Thank you.

 

[Akuini]

Akuini,

This case is considered as resolved, but if I were to expand the range from currently 2 columns to 5 columns, where in the code do i make the edit?

Change this part:

 For q = 1 To 2

to this:

 For q = 1 To f.Columns.Count

It will adjust the code to match the number of columns in the range you specify in this part:

Set f = Range("Table1[[WORKLOAD_DRIVERS]:[DATA_INPUT]]")

 

[alishern]

Change this part:

 For q = 1 To 2

to this:

 For q = 1 To f.Columns.Count

It will adjust the code to match the number of columns in the range you specify in this part:

Set f = Range("Table1[[WORKLOAD_DRIVERS]:[DATA_INPUT]]")

Great! Thank you sir!

 

[Akuini]

You're welcome.