Having a problem debugging using SendKeys

[pglufkin]

Hopefully this is something real simple that I am overlooking.

I need to eliminate a paragraph marker which is the last character at the end of my text in a cell within excel. In order to keep my formatting of text within a cell (different colors of text within the same string in the cell), i believe i must use SendKeys (I stand to be corrected on this). All other solutions I have tried to eliminate the end marker makes me lose my different colors in the paragraph I have formatted the text with. The SendKeys approach solves this problem. The SendKeys code I am using to eliminate the last character is:

Application.SendKeys "{F2}"
Application.SendKeys "{Backspace}"
Application.SendKeys "{Enter}"

Problem is, I am having to debug and when I come across these sendkey strokes in F8 step mode, it sends the strokes to the vbe and ceases processing them in the spreadsheet. How do I keep the sendkeys working on the spreadsheet while i am stepping through vbe debugging? thx

 

[John_w]

Another reason for not using SendKeys. Try this instead, assuming the text is in cell A1:

Sub DLCC()
    Dim i As Integer
    With Range("A1")
        For i = 1 To .Characters.Count - 1
            .Characters(i, 1).Text = .Characters(i, 1).Text
            .Characters(i, 1).Font.Name = .Characters(i, 1).Font.Name
            .Characters(i, 1).Font.FontStyle = .Characters(i, 1).Font.FontStyle
            .Characters(i, 1).Font.Size = .Characters(i, 1).Font.Size
            .Characters(i, 1).Font.Strikethrough = .Characters(i, 1).Font.Strikethrough
            .Characters(i, 1).Font.Superscript = .Characters(i, 1).Font.Superscript
            .Characters(i, 1).Font.Subscript = .Characters(i, 1).Font.Subscript
            .Characters(i, 1).Font.OutlineFont = .Characters(i, 1).Font.OutlineFont
            .Characters(i, 1).Font.Shadow = .Characters(i, 1).Font.Shadow
            .Characters(i, 1).Font.Underline = .Characters(i, 1).Font.Underline
            .Characters(i, 1).Font.ColorIndex = .Characters(i, 1).Font.ColorIndex
        Next
        .Characters(i, 1).Text = ""     'delete last character in cell
    End With
End Sub

 

[pglufkin]

Thanks so much. It looks like this will work. I will try it tomorrow and let you know.

 

[pglufkin]

This procedure takes too long. I have literally millions of characters to go through.

 

[John_w]

Try this instead:

Sub DLCC2()
    
    With Range("A1")
        .Characters(.Characters.Count, 1).Text = ""     'delete last character in cell
    End With
    
End Sub

And for all populated cells on the active sheet:

Sub DLCC_All()
    
    Dim cell As Range
    
    Application.ScreenUpdating = False
    For Each cell In ActiveSheet.UsedRange
        If cell.Text <> "" Then cell.Characters(cell.Characters.Count, 1).Text = ""
    Next
    Application.ScreenUpdating = True
    
End Sub