vba - 删除子字符串但保留格式

Question

我有一小段代码可以从我选择的单元格中删除子字符串垃圾:

Sub RemoveJunk()
    Dim r As Range
    For Each r In Selection
         r.Value = Replace(r.Value, "junk", "")
    Next r
End Sub

该代码有效，但它破坏了单元格中保留的字符的格式。所以如果我从以下开始:

我最终得到:

有什么方法可以避免扰乱保留字符的格式吗？？

Answer 1

Sub RemovePreserveFormatting(ByVal Where As Range, Expression As String, Optional ByVal Compare As VbCompareMethod = VbCompareMethod.vbBinaryCompare)
  Dim c As Range

  For Each c In Where
    Dim pos As Long: pos = 0

    Do
      pos = InStr(pos + 1, c.Value, Expression, Compare)
      If pos = 0 Then Exit Do

      c.Characters(pos, Len(Expression)).Delete
    Loop
  Next

End Sub