CODEWITHSUNDEEP
vbaexcelreplace
Ken Feier
Ken Feier

Reputation: 25

Find and Replace in VBA

I am attempting to go through a column of department names (strings) and anything that contains the string " - INACTIVE" needs to have it deleted and just have the department name. For instance, if I had "Jacksonville - INACTIVE", I would want it to simply be "Jacksonville" in the column cell. I have attempted to use the .Find and .Replace methods but I don't seem to grasp the way the way to use them.

I have attempted it with help from a friend and this is what i have so far. I am new to this language.

 Sub DeleteRows()

    Dim c As Range
    Dim SrchRng
    Dim InactiveDepartment As String
    Dim Department As String

 Range("A1").Select

 InactiveDepartment = ActiveCell.Value

 Set SrchRng = ActiveSheet.UsedRange

 Do Until ActiveCell.Value = ""
     Set c = SrchRng.Find("INACTIVE", LookIn:=xlValues)
     If Not c Is Nothing Then
         Department = Replace(InactiveDepartment, " - INACTIVE", "")
         ActiveCell.Value = Department
         ActiveCell.Offset(1, 0).Select
    Else
         ActiveCell.Offset(1, 0).Select
    End If
Loop

End Sub

Upvotes: 1

Views: 1733

Answers (1)

Uri Goren
Uri Goren

Reputation: 13700

Why not use the Excel Replace worksheet function on the entire A column ?

Range("A:A").Replace What:="- INACTIVE", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

Upvotes: 2

Related Questions