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?

• wrbp: If you don't find what you are looking for, at least change the code as that is inspecting the whole range again and again for every character you are looking for. Create a function that inspect each character in the range only once to be compared with a mask, that could better. Maybe ...
• Tim Williams: You can use a For x = 0 to 31 loop for the range, and maybe loop over an array of 127, 129, etc. You can use another array for the last 4 replacements.

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