Change recorded macro range to use selected

myfotoguy

New Member
Joined
Dec 30, 2017
Messages
3
I recorded the macro below. I would like to change it so instead of the cell range identified, I would like it to run on whatever cells I currently have selected.

Ideally, I would open a workbook with this macro, then open another workbook. In the second workbook I would highlight a range of cells in a column (city names) then run this macro against what I have selected (instead of the cell range the macro recorded).

I thought I saw code that does something like this, but I don't know how to change what I have below.

Thanks for any help you can provided.

Code:
Sub CanadaReplacements()
'
' CanadaReplacements Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
    Range("A7").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$7:$A$100000").AutoFilter Field:=1, Criteria1:="=*'*", _
        Operator:=xlAnd
    Selection.Replace What:="'", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveSheet.Range("$A$7:$A$100000").AutoFilter Field:=1, Criteria1:="=*-*", _
        Operator:=xlAnd
    Selection.Replace What:="-", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveSheet.Range("$A$7:$A$100000").AutoFilter Field:=1, Criteria1:="=E **", _
        Operator:=xlAnd
    Selection.Replace What:="E ", Replacement:="East ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveSheet.Range("$A$7:$A$100000").AutoFilter Field:=1, Criteria1:="=E. **", _
        Operator:=xlAnd
    Selection.Replace What:="E. ", Replacement:="East ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveSheet.Range("$A$7:$A$100000").AutoFilter Field:=1, Criteria1:="=** Crn", _
        Operator:=xlAnd
    Selection.Replace What:=" Crn", Replacement:=" Corner", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveSheet.Range("$A$7:$A$100000").AutoFilter Field:=1
End Sub
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi & welcome to the board
One option
Code:
Sub CanadaReplacements()
'
' CanadaReplacements Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
    With Selection
      .AutoFilter
      .AutoFilter Field:=1, Criteria1:="=*'*", _
          Operator:=xlAnd
      .Replace What:="'", Replacement:="", LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .AutoFilter Field:=1, Criteria1:="=*-*", _
          Operator:=xlAnd
      .Replace What:="-", Replacement:=" ", LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .AutoFilter Field:=1, Criteria1:="=E **", _
          Operator:=xlAnd
      .Replace What:="E ", Replacement:="East ", LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .AutoFilter Field:=1, Criteria1:="=E. **", _
          Operator:=xlAnd
      .Replace What:="E. ", Replacement:="East ", LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .AutoFilter Field:=1, Criteria1:="=** Crn", _
          Operator:=xlAnd
      .Replace What:=" Crn", Replacement:=" Corner", LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .AutoFilter Field:=1
    End With
End Sub
But I'm not sure you need all those filters, so another option
Code:
Sub CanadaReplacements()
'
' CanadaReplacements Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
    With Selection
      .Replace What:="'", Replacement:="", LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="-", Replacement:=" ", LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="E ", Replacement:="East ", LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="E. ", Replacement:="East ", LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:=" Crn", Replacement:=" Corner", LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
    End With
End Sub
 
Upvote 0
Thanks Fluff!

I'll try it without the filters too.

When I was manually running the replace I had to filter so I only replaced city names beginning with or ending with, etc. For example (not in my code above, which is only a sample of all of it) Say I want to replace St John with Saint John. I was looking for ST with a space after and replacing with Saint with a space after it. Without filtering first my replace command would make city names like First Prairie into FirSaint Prarie. Maybe My replace command was not intelligent enough though.

Thanks again, I appreciate the help!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top