VBA Characters limit of 256 within cells

Mattcarley724

New Member
Joined
Jun 4, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
What happens when you get to 257? I am able to reference .Characters well past 256 in a test.
 
Upvote 0
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.
 
Upvote 0
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

VBA Code:
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?
 
Upvote 0
VBA Code:
  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.
 
Upvote 0
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

VBA Code:
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.
 
Upvote 0
@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.
 
Upvote 0

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top