New to Excel VBA

[ssinfield]

Hi All,

I am new to Excel VBA and am having an issue with a macro I am writing, I can get VBA to delete duplicate rows but I have a situation where the same Unique ID that I am looking for can be used for multiple vendors within the same data set.

Vendor ID TransID
41122 A443111111
41122 A432113333
41122 A443111111 - Duplicate - to delete
41122 A432113333 - Duplicate - to delete

41188 A443111111
41188 A432113333

What I am trying to do is retain is the following:

41122 A443111111
41122 A432113333
41188 A443111111
41188 A432113333

The delete line code i am currently using does not loop through each vendor and removes duplicate transactions that have the same ID for different vendors.

Not sure how to resolve this. Someone's assistance would be greatly appreciated

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG25Jul22
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Twn         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nRng        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Dn = vbNullString [COLOR="Navy"]Then[/COLOR]
    Twn = Dn & Dn.Offset(, 1)
        [COLOR="Navy"]If[/COLOR] Not .Exists(Twn) [COLOR="Navy"]Then[/COLOR]
            .Add Twn, Nothing
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Dn
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
  [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ssinfield]

Hi Mick,

Thank you for your response, I try that code and it didn't quite work.

I have Vendor Number in column X and TransID in Column Y I can't actually see where you declare the Y column to find the duplicate within the vendor.

Thank you heaps for your assistance.

Try:-

[COLOR=Navy]Sub[/COLOR] MG25Jul22
[COLOR=Navy]Dim[/COLOR] Rng         [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dn          [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Twn         [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] nRng        [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.count).End(xlUp))
[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Not Dn = vbNullString [COLOR=Navy]Then[/COLOR]
    Twn = Dn & Dn.Offset(, 1)
        [COLOR=Navy]If[/COLOR] Not .Exists(Twn) [COLOR=Navy]Then[/COLOR]
            .Add Twn, Nothing
        [COLOR=Navy]Else[/COLOR]
            [COLOR=Navy]If[/COLOR] nRng [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR]
                [COLOR=Navy]Set[/COLOR] nRng = Dn
            [COLOR=Navy]Else[/COLOR]
                [COLOR=Navy]Set[/COLOR] nRng = Union(nRng, Dn)
            [COLOR=Navy]End[/COLOR] If
        [COLOR=Navy]End[/COLOR] If
  [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]If[/COLOR] Not nRng [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR] nRng.EntireRow.Delete
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[MickG]

Change this line:-

Set Rng = Range(Range("A1"), Range("A" & Rows.count).End(xlUp))

to this:-

Set Rng = Range(Range("X1"), Range("X" & Rows.count).End(xlUp))

NB:- If you data starts on line 2 you can change the "X1" to "X2".
NB2:- If you only want to delete rows in "X & Y" chabge the last line to:_

If Not nRng Is Nothing Then nRng.Resize(, 2).Delete