How to remove text strings that exceed a certain value within specified range.

[graham.mike]

I've been looking for a soultion for my issue for some time now and was
very happy to find the following resolution posted a little over a year
ago.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const nMAX As Long = 910
Dim rCell As Range
Set Target = Intersect(Target, Columns(1))
If Not Target Is Nothing Then
For Each rCell In Target
With rCell
If Len(.Text) > nMAX Then
MsgBox "The text in cell " & _
.Address(False, False) & " is too long (" &
_
Len(.Text) & " characters)" & vbNewLine & _

"Max. Characters: " & nMAX
End If
End With
Next rCell
End If
End Sub

However, I'm looking for one modification to the code. I'd like to add
code that will remove text strings within Column A that contain more
than 910 characters after the user selects OK from the corresponding
MsgBox. Data/Validation will not work as I need to check for copy/paste
values as well. If this is not possible then, I'd like try to replace
the MsgBox code with code that will remove any text string in column A
that exceeds 910 characters.
Any help is much appreciated.
Thanks, Mike

 

[Scott]

Can you not just add the following code after the MsgBox?
Application.EnableEvents = False
rCell = ""
Application.EnableEvents = True

Scott

 

[graham.mike]

That seems to have done the trick. Thanks so much for your help.
- Mike