I have a replace function in a MACRo that works but would like to use Substitute. Can someone review and update to use Substitute?

BudZ 121 Reputation points
2022-01-31T22:00:12.503+00:00

Range(Cells(2, 11), Cells(lastRow, lastCol)).Select ' Starting in row 2 and starting 11 rows over the H will be removed in any columns and rows after that through the end of those columns and rows
Err.Clear
tmpErr = Err.Number
On Error Resume Next
Selection.Replace What:="h", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
tmpErr = Err.Number
On Error GoTo 0

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,488 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vahid Ghafarpour 17,875 Reputation points
    2023-08-14T04:54:10.6666667+00:00

    Did you try this method?

    Dim cell As Range
For Each cell In Range(Cells(2, 11), Cells(lastRow, lastCol))
    cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "h", "")
Next cell
    0 comments