Restrict find/replace region

Question

I have a macro to clean up raw input phone numbers, and it generally works great, except after the rare occasions when I've done a global (workbook) find or replace. If I have a brain fart & forget to change from "workbook" back to "sheet" mode it will run rampant, higgledy-piggledy extracting '-' & '.' out of data & formulae alike on all sheets in the workbook, irrespective of the constraints that I thought I'd applied to it.

How do I constrain it properly, so I don't have to spend a couple hours several times a year restoring?

What I have now:

Sheets("Data").Select  
Range("DataTbl[[Phone]:[Phone2]]").Select  ' DataTbl is 15 col x >800 row
Selection.Replace What:="  ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:=")", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="(", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

score 3 · Accepted Answer · edited May 23 '17 at 12:12

Stop relying on .Select and Selection to define your area of concern. It may work some or even most of the time but can only lead to eventual errors due to the inherent ambiguous nature.

Dim r As Range
On Error Resume Next
Set r = Cells.Find(What:=vbNullString, LookIn:=xlFormulas, _
                 SearchOrder:=xlRows, LookAt:=xlPart, MatchCase:=False)
On Error GoTo 0
With Sheets("Data").Range("DataTbl[[Phone]:[Phone2]]")  ' DataTbl is 15 col x >800 row
    .Replace What:="  ", Replacement:=vbNullString, LookAt:=xlPart
    .Replace What:=" ", Replacement:=vbNullString, LookAt:=xlPart
    .Replace What:=")", Replacement:=vbNullString, LookAt:=xlPart
    .Replace What:="-", Replacement:=vbNullString, LookAt:=xlPart
    .Replace What:="(", Replacement:=vbNullString, LookAt:=xlPart
    .Replace What:=".", Replacement:=vbNullString, LookAt:=xlPart
End With

See How to avoid using Select in Excel VBA macros for various methods to get away from .Select.

EDIT: Added four lines of code at the beginning to reset the 'remembered' .Find parameters to defaults (e.g. Within: Sheet, not Within: Workbook).

Unfortunately that behaved the same way. Worked fine when Find/Replace was in Sheet mode, but not in Workbook. :( — Deina Underhill, Feb 06 '15 at 00:01
@DeinaUnderhill - I've added the minimal amount of code necessary to reset the 'remembered' `.Find/.Replace` options to defaults. — , Feb 06 '15 at 01:16

Restrict find/replace region

1 Answers1