VBA Find and Replace

[Access Beginner]

Hi All,

I have recorded a macro which replaces the "Zone" with "" and Numeric Values with "".

Zone names are Zone 01 to Zone 16.

Is there a better way to do this via VBA?

This data set will grow by at least 3,000 rows per month and is an export from another product which I have no input or control over.

Sub Find_And_Replace_II()
'
' Find_And_ReplaceII Macro
'
'
    Sheets("ISR Output by Zone").Select
    Range("G1:G100000").Select
    Selection.Replace what:="Zone", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Sheets("ISR Output by Zone").Select
    Range("G1:G100000").Select
    Selection.Replace what:="1", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Sheets("ISR Output by Zone").Select
    Range("G1:G100000").Select
    Selection.Replace what:="2", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Sheets("ISR Output by Zone").Select
    Range("G1:G100000").Select
    Selection.Replace what:="3", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Sheets("ISR Output by Zone").Select
    Range("G1:G100000").Select
    Selection.Replace what:="4", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   
Sheets("ISR Output by Zone").Select
    Range("G1:G100000").Select
    Selection.Replace what:="5", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Sheets("ISR Output by Zone").Select
    Range("G1:G100000").Select
    Selection.Replace what:="6", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Sheets("ISR Output by Zone").Select
    Range("G1:G100000").Select
    Selection.Replace what:="7", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Sheets("ISR Output by Zone").Select
    Range("G1:G100000").Select
    Selection.Replace what:="8", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Sheets("ISR Output by Zone").Select
    Range("G1:G100000").Select
    Selection.Replace what:="9", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Sheets("ISR Output by Zone").Select
    Range("G1:G100000").Select
    Selection.Replace what:="0", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
End Sub

 

[sastoka]

Hi,

Try this in a copy of your workbook.

Sub Find_And_Replace_II()
'
' Find_And_ReplaceII Macro
'
Dim LastRow as Long

LastRow = Sheets("ISR Output by Zone").Cells(Rows.Count, "G").End(xlUp).Row

With Sheets("ISR Output by Zone").Range("G1:G" & LastRow)
    .Replace what:="Zone", Replacement:="", LookAt:=xlPart,SearchOrder:=xlByRows
    .Replace what:="1", Replacement:="", LookAt:=xlPart,SearchOrder:=xlByRows
    .Replace what:="2", Replacement:="", LookAt:=xlPart,SearchOrder:=xlByRows
    .Replace what:="3", Replacement:="", LookAt:=xlPart,SearchOrder:=xlByRows
    .Replace what:="4", Replacement:="", LookAt:=xlPart,SearchOrder:=xlByRows
    .Replace what:="5", Replacement:="", LookAt:=xlPart,SearchOrder:=xlByRows
    .Replace what:="6", Replacement:="", LookAt:=xlPart,SearchOrder:=xlByRows
    .Replace what:="7", Replacement:="", LookAt:=xlPart,SearchOrder:=xlByRows
    .Replace what:="8", Replacement:="", LookAt:=xlPart,SearchOrder:=xlByRows
    .Replace what:="9", Replacement:="", LookAt:=xlPart,SearchOrder:=xlByRows
    .Replace what:="0", Replacement:="", LookAt:=xlPart,SearchOrder:=xlByRows
End With

End Sub

regards,

 

[Smitty]

How's this:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Find_And_Replace_II()<br><SPAN style="color:#007F00">'   Find_And_ReplaceII Macro</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Sheets("ISR Output by Zone").Range("G:G")<br>        .Replace what:="Zone", Replacement:="", LookAt:=xlPart, _<br>            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=<SPAN style="color:#00007F">False</SPAN>, _<br>            ReplaceFormat:=False<br>        For i = 0 <SPAN style="color:#00007F">To</SPAN> 9<br>            .Replace what:=i, Replacement:="", LookAt:=xlPart, _<br>                SearchOrder:=xlByRows, MatchCase:=<SPAN style="color:#00007F">False</SPAN>, SearchFormat:=False, _<br>                ReplaceFormat:=False<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

HTH,

 

[Access Beginner]

Thanks worked great.

Cheers

 

[Access Beginner]

Thanks to you as well Smityy, work great as well