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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
872
Office Version
  1. 365
Platform
  1. Windows
What happens when you get to 257? I am able to reference .Characters well past 256 in a test.
 

Mattcarley724

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,696
Office Version
  1. 365
Platform
  1. Windows
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?
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
872
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Mattcarley724

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

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,549
Office Version
  1. 365
Platform
  1. Windows
@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.
 

Forum statistics

Threads
1,147,479
Messages
5,741,392
Members
423,657
Latest member
Medrok2021

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
Top