1

I'm looking for a macro to delete duplicates in a column, regarding their last value e.g.

DES_FFAs_556
asda_FRF_556

Because 556 is same, it should be deleted.

right now im getting the last 4 digits of each cell but i dont know how to remove duplicates with it

Sub duplicates()

Dim i As Long
Dim res As String
Dim WB As Workbook
Dim WS As Worksheet
Dim total As Long

Set WB = Workbooks("MQB37W - SW Architecture Matrix_Nw")
Set WS = WB.Sheets("SW Architecture Main - In...")

With WS
    total = .Cells(Rows.Count, 1).End(xlUp).Row

    For i = 4 To total
        res = Right(Cells(i, "A").Value, 4)
        WS.Range("A4:total").RemoveDuplicates Columns:=1, Header:=xlNo
    Next
End With
End Sub
Tim Stack
  • 3,209
  • 3
  • 18
  • 39
  • You can't use `RemoveDuplicates` for this. I'd probably build up a `Union` of the cells to delete and then delete at the end. – BigBen Feb 19 '20 at 14:29
  • could you give me an example? i dont quite understand –  Feb 19 '20 at 14:32
  • [This](https://stackoverflow.com/a/59975507/9245853) is a recent answer showing how to use `Union` in the case. – BigBen Feb 19 '20 at 14:34
  • Actually, the whole idea cannot work, because they are no real duplicates. Which do you actually want to delete and which to keep? I mean if you say `DES_FFAs_556` and `asda_FRF_556` are duplicates because the last 4 characters are the same. How do you decide which of both you keep? Or should even both be deleted? Please clarify. – Pᴇʜ Feb 19 '20 at 16:43
  • First I wanna check for the number. It is the same I will clarify which prefix zu keep. All others in the column shall be deleted –  Feb 20 '20 at 06:45
  • So now you say *"All others in the column shall be deleted'* that means you want to **keep** the duplicates and want to delete all the other? Really you need to be much more clear about what you *actually* want to do. Please give a proper example showing which will be deleted and wich will be kept and explain for each item why it is kept or deleted. Otherwise I see no chance to help you. We need to know the ruls for the game we play. – Pᴇʜ Feb 20 '20 at 08:05
  • no sorry for unclearness. so if there are more than 1 entries with the ending "....556", then only the one with lets say prefix "ABC_FFAS****_556" shall be kept and all others deleted. –  Feb 20 '20 at 08:08

1 Answers1

0

You do not need VBA for this. You can just use code from this tutorial in a new column and than based on that column you can filter, conditional format or delete rows. If you would like to indicate only rows after the first occurrence you can use COUNTIF. Ofcourse if you need VBA for something else you can apply the same logic I described above inside the VBA code.

radulle
  • 1,437
  • 13
  • 19