Geek Answers Handbook

Excel: searching and replacing a macro - only one column

I wrote several macros to format the data loading in the same accepted format, from the program we are pulling, it refuses to extract the data as we want, but theoretically it will not be difficult to change in Excel.

The way it runs is to have separate macros for the modifiers, and then the Run All macro, which simply makes a call for everyone.

I currently have:

Sub ReplaceTitleMs()
'
' Strips Mrs from Headteacher Name
'
'
'
 Columns("V").Select
 Cells.Replace What:="Ms ", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

But when I run this, it removes Ms from the whole sheet, and one column requires that Ms is still in the cells (this is column W)

Sample data is effective:

Ms Helen Smith
Ms Brenda Roberts
Ms Kirsty Jones

But there are many other headers, so I would just like to run Find and Replace in the column that should be selected by the macro.

, ... !

+4
2

Replace()

Sub ReplaceTitleMs()
    Columns("V").Replace    What:="Ms ", _
                            Replacement:="", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False
End Sub
+7

( ), , MatchCase True, Mrs Nancy Adams Mrs Nancy Ada

Columns("V").Replace "Ms ", vbNullString, xlPart, xlByRows, True

,

  • AutoFilter
  • Find FindNext, , .
+4

More articles:


All Articles