Change recorded macro range to use selected

[myfotoguy]

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.

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

 

[myfotoguy]

Note, in my code above

Range("A7").Select​

should be

Range("A7:A100000").Select

​

 

[Fluff]

Hi & welcome to the board
One option

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

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

 

[myfotoguy]

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!