(VBA) How to account for variables in text?

[moddddmyexcel]

A wonderful mod helped me out with the code below to remove duplicates but make an exception for fields starting with VNA. I now modified the code to include other locations that are variable. I was wondering if there was anyway I could shorten this codes to account for *TRN* and *EVN* where the asterisks are variable text.

Sub RemoveDups()
 With Sheets("Replen Data")
    With .Range("A4", .Range("L" & Rows.Count).End(xlUp))
      .Columns(12).Value = .Worksheet.Evaluate(Replace("if(left(#,3)=""VNA"",#&""||""&ROW(#),#)", "#", .Columns(12).Address))
      .Columns(12).Value = .Worksheet.Evaluate(Replace("if(left(#,4)=""1TRN"",#&""||""&ROW(#),#)", "#", .Columns(12).Address))
      .Columns(12).Value = .Worksheet.Evaluate(Replace("if(left(#,4)=""2TRN"",#&""||""&ROW(#),#)", "#", .Columns(12).Address))
      .Columns(12).Value = .Worksheet.Evaluate(Replace("if(left(#,4)=""3TRN"",#&""||""&ROW(#),#)", "#", .Columns(12).Address))
      .Columns(12).Value = .Worksheet.Evaluate(Replace("if(left(#,4)=""4TRN"",#&""||""&ROW(#),#)", "#", .Columns(12).Address))
      .Columns(12).Value = .Worksheet.Evaluate(Replace("if(left(#,4)=""5TRN"",#&""||""&ROW(#),#)", "#", .Columns(12).Address))
      .Columns(12).Value = .Worksheet.Evaluate(Replace("if(left(#,4)=""6TRN"",#&""||""&ROW(#),#)", "#", .Columns(12).Address))
      .Columns(12).Value = .Worksheet.Evaluate(Replace("if(left(#,4)=""1EVN"",#&""||""&ROW(#),#)", "#", .Columns(12).Address))
      .Columns(12).Value = .Worksheet.Evaluate(Replace("if(left(#,4)=""2EVN"",#&""||""&ROW(#),#)", "#", .Columns(12).Address))
      .Columns(12).Value = .Worksheet.Evaluate(Replace("if(left(#,4)=""3EVN"",#&""||""&ROW(#),#)", "#", .Columns(12).Address))
      .Columns(12).Value = .Worksheet.Evaluate(Replace("if(left(#,4)=""4EVN"",#&""||""&ROW(#),#)", "#", .Columns(12).Address))
      .Columns(12).Value = .Worksheet.Evaluate(Replace("if(left(#,4)=""5EVN"",#&""||""&ROW(#),#)", "#", .Columns(12).Address))
      .Columns(12).Value = .Worksheet.Evaluate(Replace("if(left(#,4)=""6EVN"",#&""||""&ROW(#),#)", "#", .Columns(12).Address))
      .RemoveDuplicates Columns:=Array(1, 4, 12), Header:=xlNo
      .Columns(12).Replace What:="||*", Replacement:="", LookAt:=xlPart
      .RemoveDuplicates Columns:=Array(1, 4, 10), Header:=xlNo
      .RemoveDuplicates Columns:=Array(1, 5), Header:=xlNo
    End With
    End With
Exit Sub

 

[kevin9999]

The MID() function should work for you. For example:

.Worksheet.Evaluate(Replace("if(mid(#,2,3)=""TRN"",#&""||""&ROW(#),#)", "#", .Columns(12).Address))

Where 2 is the starting character in the string to check and 3 is the number of characters to compare. Just change TRN for EVN for the second Evaluate.

 

[moddddmyexcel]

The MID() function should work for you. For example:

.Worksheet.Evaluate(Replace("if(mid(#,2,3)=""TRN"",#&""||""&ROW(#),#)", "#", .Columns(12).Address))

Where 2 is the starting character in the string to check and 3 is the number of characters to compare. Just change TRN for EVN for the second Evaluate.

I need to familiarize myself with the MID() function. Such a simple fix, thank you!

 

[kevin9999]

I need to familiarize myself with the MID() function. Such a simple fix, thank you!

Happy to have helped