VBA Characters limit of 256 within cells

[Mattcarley724]

Hello,

I am currently working on a project that calls to delete all strikethrough characters from a large excel sheet.
I have the code below working but only up to a limit of 256 characters, I believe it has something to do with cells.Characters but I have been unable to find a work around.

Sub RemoveText()
  Dim X As Long, Cell As Range
  For Each Cell In Selection
    For X = Len(Cell.Text) To 1 Step -1
      If Cell.Characters(X, 1).Font.Strikethrough = True Then Cell.Characters(X, 1).Text = ""
      
      ' Clean up spaces
      If X > 1 Then
        If Cell.Characters(X - 1, 2).Text = "  " Then
          Cell.Characters(X, 1).Text = ""
        End If
      ElseIf Cell.Characters(X, 1).Text = " " Then
        Cell.Characters(X, 1).Text = ""
      End If
    Next
  Next
End Sub

Thank you in advance.

 

[6StringJazzer]

What happens when you get to 257? I am able to reference .Characters well past 256 in a test.

 

[Mattcarley724]

When I try tried on my excel spreadsheet the box would remain unchanged as if the macro did nothing, but when I step into the debugger it showed that is was going through recognizing that the characters where striked but it did not delete them.

 

[Peter_SSs]

I am able to reference .Characters well past 256 in a test.

Jeff, try this test
A1: =REPT("F",255)
A2: =REPT("F",256)
Now for A1:A2 copy/paste values
Then run this code & look at the results

Sub Testing()
  Range("A1").Characters(255, 1).Text = "X"
  Range("A2").Characters(256, 1).Text = "X"
End Sub

@Mattcarley724
Are there other characters in the cells that are individually formatted other than by strikethrough where that formatting needs to be retained?
If not, you could manipulate the cell text as a string to remove the strikethrough characters and then put the (possibly) shortened sting value back in the cell.

For example could a cell contain this?
abcdef
If so, does the "b" need to retain its bold and red to return "abcef" or would "abcef" be sufficient?

 

[6StringJazzer]

  Range("A1").Characters(255, 1).Text = "X"

I get 1004 "Unable to set the Text property of the Characters class" on the first line of code. In my quick test I only iterated through the characters, not trying to change them. However, here I cannot change one even on this string of 255.

 

[Mattcarley724]

Jeff, try this test
A1: =REPT("F",255)
A2: =REPT("F",256)
Now for A1:A2 copy/paste values
Then run this code & look at the results

Sub Testing()
  Range("A1").Characters(255, 1).Text = "X"
  Range("A2").Characters(256, 1).Text = "X"
End Sub

@Mattcarley724
Are there other characters in the cells that are individually formatted other than by strikethrough where that formatting needs to be retained?
If not, you could manipulate the cell text as a string to remove the strikethrough characters and then put the (possibly) shortened sting value back in the cell.

For example could a cell contain this?
abcdef
If so, does the "b" need to retain its bold and red to return "abcef" or would "abcef" be sufficient?

Unfortunately the rest of the cell does need to contain its formatting.

 

[Akuini]

@Mattcarley724
My suggestion:
Copy-paste your data to Word, you can use the Replace dialogue box to remove all strikethrough characters in one go, then copy-paste back to excel.

 

[Peter_SSs]

I get 1004 "Unable to set the Text property of the Characters class" on the first line of code.

I think that you might have omitted one of the steps in my suggested test.

Now for A1:A2 copy/paste values