vba replace multiple Chr(xx) in one specific range

Question

I have this segment in my code that I have to remove some characters from a range.
Instead of looping through every character inside every single cell in the range, I could just use the .Replace method, right?

Using regex is not an option.

Dim subst_ni As Range: Set subst_ni = ActiveSheet.Range("G2", ActiveSheet.Range("G" & ActiveSheet.Rows.Count).End(xlUp))
With subst_ni
    .Replace What:=Chr(65), Replacement:="@"
    .Replace What:=Chr(69), Replacement:="@"
    .Replace What:=Chr(73), Replacement:="@"
    .Replace What:=Chr(79), Replacement:="@"
    .Replace What:=Chr(85), Replacement:="@"
    .Replace What:=".", Replacement:=vbNullString
    .Replace What:="/", Replacement:=vbNullString
    .Replace What:=":", Replacement:=vbNullString
    .Replace What:=";", Replacement:=vbNullString
End With

But, I have several replacements to do with the same range.

The issue I have is with the Chr(xx) part, where I have the chance to remove non-printable bits of the range value, using the XX as "0 to 31, 127, 129, 141, 143, 144, 157" and some other specific chars I can't use.
How may we write it in a better way instead having a single code line for each XX?

Answer 1

The way you're writing it now is probably efficient enough (it will depend on the size of the data-set you're working with). What you may be seeking is to extract that chunk of code into it's own subroutine so you can perform the same action on multiple ranges.

If that's the case, create a module and drop your code into a new subroutine. I called this one CleanRange:

(I also added an example of the 0 to 31 you asked about, excluding 0 and 1 as they don't play nice with .Replace)

Public Sub CleanRange(ByRef TheRange As Range)
    
  With TheRange
    
    .Replace What:=Chr(65), Replacement:="@"
    .Replace What:=Chr(69), Replacement:="@"
    .Replace What:=Chr(73), Replacement:="@"
    .Replace What:=Chr(79), Replacement:="@"
    .Replace What:=Chr(85), Replacement:="@"
    .Replace What:=".", Replacement:=vbNullString
    .Replace What:="/", Replacement:=vbNullString
    .Replace What:=":", Replacement:=vbNullString
    .Replace What:=";", Replacement:=vbNullString
    
    'example of iterating through a range of characters
    For x = 2 To 31
      .Replace What:=Chr(x), Replacement:="@"
    Next x
    
  End With
  
End Sub

Now you can use the new sub CleanRange by passing your range to it. Examples below:

Private Sub CleanAllTheRanges()
  
  With ActiveSheet
    CleanRange .Range("C2", .Range("C" & .Rows.Count).End(xlUp))
  End With
  
  With Worksheets("different sheet name")
    CleanRange .Range("G2", .Range("G" & .Rows.Count).End(xlUp))
  End With
  
  With Worksheets("another sheet name")
    CleanRange Intersect(.Range("G:G"), .UsedRange)
  End With
  
End Sub