(VBA) How can I remove duplicates but exclude rows with a specific value in a column?

[moddddmyexcel]

My current code is below. The problem is in column 9 I have over 2000 possible different values but I do not want to remove duplicates for rows that contain "VNASTG" in column 9.

Sheets("Data").Range("A4:I300000").RemoveDuplicates Columns:=Array(1, 2, 9), Header:= _
xlYes

 

[DanteAmor]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Please try the following macro on a copy of your data. Also try the total number of your records and check the performance of the macro.

Sub RemoveDuplicatesExcep()
  Dim dic As Object
  Dim a As Variant, b As Variant, ky As Variant
  Dim i&, j&, k&
  
  a = Range("A5", Range("I" & Rows.Count).End(3)).Value2
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  Set dic = CreateObject("Scripting.Dictionary")
  
  For i = 1 To UBound(a, 1)
    ky = a(i, 1) & "|" & a(i, 2) & "|" & a(i, 9)
    If a(i, 9) = "VNASTG" Or Not dic.exists(ky) Then
        dic(ky) = Empty
        k = k + 1
        For j = 1 To UBound(a, 2)
          b(k, j) = a(i, j)
        Next
    End If
  Next
  
  Range("A5").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

I hope to hear from you soon.
Respectfully
Dante Amor

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming those values in column I are not the result of formulas, you could try this with a copy of your workbook.
It simply
- makes all the VNASTG values unique by appending "||" and the row number,
- doing the remove duplicates and then
- removing that appended text

Sub RemoveDupesWithException()
  With Sheets("Data")
    With .Range("A4", .Range("I" & Rows.Count).End(xlUp))
      .Columns(9).Value = .Worksheet.Evaluate(Replace("if(#=""VNASTG"",""VNASTG||""&ROW(#),#)", "#", .Columns(9).Address))
      .RemoveDuplicates Columns:=Array(1, 2, 9), Header:=xlYes
      .Columns(9).Replace What:="||*", Replacement:="", LookAt:=xlPart
    End With
  End With
End Sub

 

[kevin9999]

Deleted

 

[moddddmyexcel]

If a(i, 9) = "VNASTG" Or Not dic.exists(ky) Then

Works great! How would I possibly add multiple criteria to this? Say there are additional locations I want to exclude as well like a VNASTG2.

 

[kevin9999]

Welcome to the MrExcel board!

Assuming those values in column I are not the result of formulas, you could try this with a copy of your workbook.
It simply
- makes all the VNASTG values unique by appending "||" and the row number,
- doing the remove duplicates and then
- removing that appended text

Sub RemoveDupesWithException()
  With Sheets("Data")
    With .Range("A4", .Range("I" & Rows.Count).End(xlUp))
      .Columns(9).Value = .Worksheet.Evaluate(Replace("if(#=""VNASTG"",""VNASTG||""&ROW(#),#)", "#", .Columns(9).Address))
      .RemoveDuplicates Columns:=Array(1, 2, 9), Header:=xlYes
      .Columns(9).Replace What:="||*", Replacement:="", LookAt:=xlPart
    End With
  End With
End Sub

Very perspicacious solution Peter

 

[moddddmyexcel]

.Columns(9).Value = .Worksheet.Evaluate(Replace("if(#=""VNASTG"",""VNASTG||""&ROW(#),#)", "#", .Columns(9).Address))

I have multiple VNASTG like VNASTG1, VNASTG2, VNASTG3. (Learned this after the post) How can I incorporate multiple criteria? It could just be based on the first 3 letters being VNA if that makes things easier.

 

[Peter_SSs]

I have multiple VNASTG like VNASTG1, VNASTG2, VNASTG3. (Learned this after the post) How can I incorporate multiple criteria? It could just be based on the first 3 letters being VNA if that makes things easier.

For the original question asked, did my code also work as required? (No use taking on this additional requirement if the first one was not met. )

 

[moddddmyexcel]

For the original question asked, did my code also work as required? (No use taking on this additional requirement if the first one was not met. )

Yes it did. I was hoping to alter it where everything after VNA was variable like VNA* to VNA(Row #), data retention is not necessary.

 

[Peter_SSs]

This small change should do it I think.

Sub RemoveDupesWithException_v2()
  With Sheets("Data")
    With .Range("A4", .Range("I" & Rows.Count).End(xlUp))
      .Columns(9).Value = .Worksheet.Evaluate(Replace("if(left(#,3)=""VNA"",#&""||""&ROW(#),#)", "#", .Columns(9).Address))
      .RemoveDuplicates Columns:=Array(1, 2, 9), Header:=xlYes
      .Columns(9).Replace What:="||*", Replacement:="", LookAt:=xlPart
    End With
  End With
End Sub