Remove Initials if criteria is met

Question

Need help creating a macro which removes initials (example : "T.") from column H if column J equals to "Company One".

I've tried the code below, but it has no effect. How can I go about this?

Option Explicit

Public Sub removeInitials()

    With ThisWorkbook.ActiveSheet.UsedRange
        If ActiveSheet.AutoFilter Is Nothing Then .AutoFilter
        .AutoFilter Field:=10, Criteria1:="Company One"
        .Columns(8).Offset(2).Replace What:=" *[A-Z].", Replacement:=""
        .AutoFilter
    End With

End Sub

`Range.Replace()` only uses `*` and `?` as wildcards. It doesn't support things like `[A-Z]` You will need to look over the visible cells in that column and remove the initials if they're present. — Tim Williams, Jul 27 '22 at 00:25

score 2 · Answer 1 · answered Jul 27 '22 at 01:22

If you don't have to large a database this should do the trick w/o messing with filters. Note: I changed the column for Employer for my example. I also changed the data a little to show it works thus the inclusion of the starting/ending points.

Public Sub removeInitials()

   Dim wks  As Worksheet
   Dim lRow As Long
   
   Set wks = ActiveSheet
   lRow = 2
    With wks
        Do While (.Cells(lRow, 1) <> "")
         If (.Cells(lRow, 3) = "Company Two" And Right(.Cells(lRow, 1), 1) = ".") Then
           Debug.Print "Current Row " & lRow
           .Cells(lRow, 1) = Left((.Cells(lRow, 1)), Len(.Cells(lRow, 1)) - 3)
         End If
         Debug.Print lRow
         lRow = lRow + 1
        Loop
    End With

End Sub 'removeInitials

Starting Point

Ending Point

score 2 · Answer 2 · answered Jul 27 '22 at 01:45

If you want to avoid VBA, you could just do a formula...put the below in cell K2 (or wherever...) and drag down.

=TRIM(IF(J2="Company One",SUBSTITUTE(H2,IFERROR(MID(H2,FIND(".",H2)-1,2),""),""),H2))

You could also have spill range as shown in column L in screen shot.

=FILTER(TRIM(IF(J2:J9999="Company One",SUBSTITUTE(H2:H9999,IFERROR(MID(H2:H9999,FIND(".",H2:H9999)-1,2),""),""),H2:H9999)),H2:H9999<>"")

If you really want a macro, here's a dynamic one that is pretty straight forward. A perfect answer would do an array, but looping through helps see what's happening.

Sub doReplace()
    Dim changeRange As Range, aCell As Range, aPosition As Long
    Const companySkip = "Company One"
    
    Set changeRange = Intersect(Range("H:H"), ActiveSheet.UsedRange).Offset(1, 0)
    
    For Each aCell In changeRange.Cells
        If aCell.Offset(0, 2).Value = companySkip Then
            aPosition = InStr(1, aCell.Value, ".", vbBinaryCompare)
            
            If aPosition > 0 Then
                aCell.Value = Trim(Replace(aCell.Value, Mid(aCell.Value, aPosition - 1, 2), ""))
                
            End If
            
        End If
    Next aCell

End Sub

score 2 · Answer 3 · answered Jul 27 '22 at 02:38

2

I made a few changes, and this worked:

Public Sub removeInitials()

Dim rng As Range
Set rng = ActiveSheet.UsedRange

With rng
    .AutoFilter Field:=3, Criteria1:="Company One"
    .Columns.Item(1).Replace What:=" ?.", Replacement:=""
    .AutoFilter
End With

End Sub

And here is a before execution / after execution snapshot for a comparable example:

What I had to change to get it working:

The first parameter you are passing in the replace method wasn't working for me: " *[A-Z]."

Here is a very useful thread regarding pattern syntax, specifically for the replace method if you're interested: How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

I wasn't sure why you used ".Columns(8).Offset(2)" (granted, I can't see the context of your Excel worksheet - but it seemed like you needed to return a range object for the single column with the names only - which you could then run the replace method on. That's what I did using ".Columns.Item(1)"

answered Jul 27 '22 at 02:38

Kyle Deer

91
8

1

Thank you this works! but im getting `Error 1004: AutoFilter method of Range class failed`. here and there when i run this macro – Miguel Angel Quintana Jul 28 '22 at 19:01
I know that error would result if you converted the range to a table for example. That may be the cause, in which case you can simply convert the table back to a range or modify macro to work with a table. If you know this isn't the case, it's hard to say what the cause is without additional details: copy of your macro now - explanation/visual example of your worksheet. – Kyle Deer Jul 29 '22 at 16:43
Also, make sure you remove your filters in your active sheet - then run your macro. Why: calling the ".AutoFilter" method is adding filters, then removing them once you call it again. If you already have auto filters turned on in your worksheet, depending on how you have them configured (e.g., they don't cover all of the columns in your "UsedRange", which is every cell you have used in the active sheet), then that will also return your Error 1004. – Kyle Deer Jul 29 '22 at 16:58

Remove Initials if criteria is met

3 Answers3