Loose format

[joan12]

Hello,
I have implemented a simple VBA code that deletes the word "hello" from all the cells in column E.

Sub DeleteHelloInColumnE()
    Dim ws As Worksheet
    Dim cell As Range
    
    Set ws = ActiveSheet
    
    For Each cell In ws.Range("E1:E" & ws.Cells(ws.Rows.Count, "E").End(xlUp).Row)
  
        cell.Value = Replace(cell.Value, "hello", "")
    Next cell
End Sub

The issue is that when I run the code, it also removes the formatting of words that are bold or italic. It appears that running VBA code to delete or replace content inside cells also removes the formatting.

Is there a solution to preserve formatting?

Thank you.

 

[joan12]

I have tried this code:

Sub DeleteHelloInColumnE()
    Dim ws As Worksheet
    Dim cell As Range
    Dim text As String
    Dim startPos As Long

    Set ws = ActiveSheet
    
    For Each cell In ws.Range("E1:E" & ws.Cells(ws.Rows.Count, "E").End(xlUp).Row)
        text = cell.Value
        startPos = InStr(1, text, "hello", vbTextCompare)
        
        Do While startPos > 0
            cell.Characters(startPos, Len("hello")).Delete
            text = cell.Value
            startPos = InStr(1, text, "hello", vbTextCompare)
        Loop
    Next cell
End Sub

However, when there are 259 characters, it works, but with 260 characters or more, the app freezes, and I need to kill and restart it.