Run Macro Only In Highlighted Column

[BWMagee]

Hi, when I run the following Macro it runs for the entire worksheet, however I only want it to run in the highlighted column (eg. If I click on column "H" to select the whole column, I do not want the macro to run outside that column).

Here is the macro:


Sub ReplacNames()
'
' ReplaceNames Macro
'
' Keyboard Shortcut: Option+Cmd+Shift+R
'
Cells.Replace What:="NZL", Replacement:="NZ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="AUS", Replacement:="AU", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

End Sub

Any help much appreciated!

 

[Marcelo Branco]

Maybe...

Sub ReplacNames()
'
' ReplaceNames Macro
'
' Keyboard Shortcut: Option+Cmd+Shift+R
'
With Selection.Cells
    .Replace What:="NZL", Replacement:="NZ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    .Replace What:="AUS", Replacement:="AU", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
End With

End Sub

M.

 

[BWMagee]

Maybe...

Sub ReplacNames()
'
' ReplaceNames Macro
'
' Keyboard Shortcut: Option+Cmd+Shift+R
'
With Selection.Cells
    .Replace What:="NZL", Replacement:="NZ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    .Replace What:="AUS", Replacement:="AU", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
End With

End Sub

M.

Thanks for the response. Unfortunately it didn't work. Still changed cells that were not selected.

 

[vcoolio]

Hello BWMagee,

I tested Marcelo's code and it worked seamlessly.

You could also try by using an input box:-

Sub ReplacNames()

Dim actColumn As String
actColumn = InputBox("Please enter the column letter to be activated.")

With Sheet1.Columns(actColumn)
     .Replace What:="NZL", Replacement:="NZ", LookAt:=xlPart, _
     SearchOrder:=xlByRows, MatchCase:=False
     .Replace What:="AUS", Replacement:="AU", LookAt:=xlPart, _
     SearchOrder:=xlByRows, MatchCase:=False
End With

End Sub

Cheerio,
vcoolio.

 

[My Aswer Is This]

Same here:

I tested Marcelo's code and it worked seamlessly.
​

 

[Marcelo Branco]

Thanks for the response. Unfortunately it didn't work. Still changed cells that were not selected.

Have you inserted the dot (in red) before .Replace?

M.

 

[BWMagee]

Hello BWMagee,

I tested Marcelo's code and it worked seamlessly.

Same here:

I tested Marcelo's code and it worked seamlessly.
​

Have you inserted the dot (in red) before .Replace?

M.

Ah, my mistake! I was typing:

With Selection.Cells
Cells.replace

Instead of:

With Selection.Cells
.Replace

Works perfectly now, thanks so much!