Macro almost works. Doesn't delete as desired

Yuenk · Dec 1, 2014

Hello all,

I have a macro that deletes duplicate versions of a work number. The macro runs but when it goes to deletes the data it doesnt delete the data in columns A (Proper/Error) & B (Local District).

Column AI (Rank) has the formula of =HLOOKUP(K2,$AK$1:$AN$2,2,0) that refers to column K (Revision Status). The revision status is given a rank based on a table. The reason is that when duplicate revisions are encountered it will keep the highest ranked revision. A rank of 1 is the lowest and 4 being the highest.

Table:
[TABLE="width: 192"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]Initiate
[/TD]
[TD="width: 64, bgcolor: transparent"]Working
[/TD]
[TD="width: 64, bgcolor: transparent"]Approved
[/TD]
[TD="width: 64, bgcolor: transparent"]Official
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 2411"]
<TBODY>[TR]
[TD]Proper/Error 
[/TD]
[TD]Local District
[/TD]
[TD]Work Number
[/TD]
[TD]WN Status
[/TD]
[TD]WN Type
[/TD]
[TD]WR Class
[/TD]
[TD]Area
[/TD]
[TD]Version
[/TD]
[TD]WR hist Rev #
[/TD]
[TD]Official Version?
[/TD]
[TD]Revision Status
[/TD]
[TD]ID
[/TD]
[TD]CU Location Status
[/TD]
[TD]CU Dsgn Action
[/TD]
[TD]Dsgn QTY
[/TD]
[TD]CU AB Action
[/TD]
[TD]AB QTY
[/TD]
[TD]AB Addition Y/N
[/TD]
[TD]Dsgn #
[/TD]
[TD]AB #
[/TD]
[TD]CU Completion Accept
[/TD]
[TD]Asset #
[/TD]
[TD]Serial Number
[/TD]
[TD]Facility Type
[/TD]
[TD]Address
[/TD]
[TD]Date of Location Status Change
[/TD]
[TD]WN Entry Date
[/TD]
[TD]Updated By
[/TD]
[TD]Selection Completed By
[/TD]
[TD]Selection Entry Date
[/TD]
[TD]Scheduling 
[/TD]
[TD]Scheduling 
[/TD]
[TD]As Built 
[/TD]
[TD]As Built
[/TD]
[TD]Rank
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]SCH
[/TD]
[TD]E_EO
[/TD]
[TD]OH
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]OP451
[/TD]
[TD]Design
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1859500
[/TD]
[TD]1859500
[/TD]
[TD][/TD]
[TD]12551
[/TD]
[TD]591229
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD]10/10/14
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD]Name
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]SCH
[/TD]
[TD]E_EO
[/TD]
[TD]OH
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]OP451
[/TD]
[TD]Proposed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1859511
[/TD]
[TD]1859511
[/TD]
[TD][/TD]
[TD]12555
[/TD]
[TD]591028
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD]10/10/14
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD]Name
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]SCH
[/TD]
[TD]E_EO
[/TD]
[TD]OH
[/TD]
[TD]9
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]OP451
[/TD]
[TD]Proposed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD]1859534
[/TD]
[TD]1859534
[/TD]
[TD][/TD]
[TD]12544
[/TD]
[TD]589053
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD]10/10/14
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD]Name
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]SCH
[/TD]
[TD]E_EO
[/TD]
[TD]OH
[/TD]
[TD]8
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]OP451
[/TD]
[TD]Proposed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD]1859528
[/TD]
[TD]1859528
[/TD]
[TD][/TD]
[TD]26447
[/TD]
[TD]642990
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD]10/10/14
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD]Name
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]SCH
[/TD]
[TD]E_EO
[/TD]
[TD]OH
[/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]OP451
[/TD]
[TD]Proposed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD]1859511
[/TD]
[TD]1859511
[/TD]
[TD][/TD]
[TD]12555
[/TD]
[TD]591028
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD]10/10/14
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD]Name
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]SCH
[/TD]
[TD]E_EO
[/TD]
[TD]OH
[/TD]
[TD]8
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]OP451
[/TD]
[TD]Proposed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD]1859528
[/TD]
[TD]1859528
[/TD]
[TD][/TD]
[TD]26447
[/TD]
[TD]642990
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD]10/10/14
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD]Name
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]SCH
[/TD]
[TD]E_EO
[/TD]
[TD]OH
[/TD]
[TD]9
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]OP451
[/TD]
[TD]Proposed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD]1859534
[/TD]
[TD]1859534
[/TD]
[TD][/TD]
[TD]12544
[/TD]
[TD]589053
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD]10/10/14
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD]Name
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]SCH
[/TD]
[TD]E_EO
[/TD]
[TD]OH
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]OP451
[/TD]
[TD]Proposed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD]1859511
[/TD]
[TD]1859511
[/TD]
[TD][/TD]
[TD]12555
[/TD]
[TD]591028
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD]10/10/14
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD]Name
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]SCH
[/TD]
[TD]E_EO
[/TD]
[TD]OH
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]OP451
[/TD]
[TD]Proposed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD]1859528
[/TD]
[TD]1859528
[/TD]
[TD][/TD]
[TD]26447
[/TD]
[TD]642990
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD]10/10/14
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD]Name
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]SCH
[/TD]
[TD]E_EO
[/TD]
[TD]OH
[/TD]
[TD]6
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]OP451
[/TD]
[TD]Proposed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD]1859534
[/TD]
[TD]1859534
[/TD]
[TD][/TD]
[TD]12544
[/TD]
[TD]589053
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD]10/10/14
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD]Name
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]SCH
[/TD]
[TD]E_EO
[/TD]
[TD]OH
[/TD]
[TD]5
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Approved
[/TD]
[TD]OP451
[/TD]
[TD]Proposed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD]N
[/TD]
[TD]1859511
[/TD]
[TD]1859511
[/TD]
[TD][/TD]
[TD]12555
[/TD]
[TD]591028
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD]10/10/14
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD]Name
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]SCH
[/TD]
[TD]E_EO
[/TD]
[TD]OH
[/TD]
[TD]8
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Approved
[/TD]
[TD]OP451
[/TD]
[TD]Proposed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD]N
[/TD]
[TD]1859528
[/TD]
[TD]1859528
[/TD]
[TD][/TD]
[TD]26447
[/TD]
[TD]642990
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD]10/10/14
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD]Name
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]SCH
[/TD]
[TD]E_EO
[/TD]
[TD]OH
[/TD]
[TD]9
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Approved
[/TD]
[TD]OP451
[/TD]
[TD]Proposed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD]N
[/TD]
[TD]1859534
[/TD]
[TD]1859534
[/TD]
[TD][/TD]
[TD]12544
[/TD]
[TD]589053
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD]10/10/14
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD]Name
[/TD]
[TD]10/02/14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3
[/TD]
[/TR]
</TBODY>[/TABLE]

Code:

Sub keep_highest()
Dim k As Object, a, al As Long
Set k = CreateObject("Scripting.dictionary")
a = Sheets("sheet1").Cells(1).CurrentRegion
For al = 2 To UBound(a)
    If a(al, 8) > k(a(al, 1)) Then k(a(al, 1)) = a(al, 8)
Next al
For al = UBound(a) To 2 Step -1
    If a(al, 8) <> k(a(al, 1)) Then Cells(al, 1).Resize(, UBound(a, 2)).Delete xlUp
Next al
End Sub

There will be thousands of lines of data. When the macro is run it needs to delete revisions 1,2, 3 but keep revision 4 in this example. Also the macro should not affect the other data unless it follows this example. The script does this but it doesn't delete the data in columns A & B.

Any suggestions are welcome, Thanks

Andrew Poulsom · Dec 1, 2014

For me your code does delete columns A and B. The deletion starts at column 1 as defined by Cells(al, 1). What range does the data you posted occupy?

Yuenk · Dec 1, 2014

Sorry that code doesnt work properly. This is the code that works but doesn't delete columns A & B.

Code:

Sub keep_highest()
Dim k As Object, a, al As Long
Set k = CreateObject("Scripting.dictionary")
a = Sheets("sheet1").Cells(1).CurrentRegion
For al = 2 To UBound(a)
    If a(al, 8) > k(a(al, 3)) Then k(a(al, 3)) = a(al, 8)
Next al
For al = UBound(a) To 2 Step -1
    If a(al, 8) <> k(a(al, 3)) Then Cells(al, 3).Resize(, UBound(a, 2)).Delete xlUp
Next al
End Sub

Andrew Poulsom · Dec 1, 2014

Does it work with Cells(al, 1) rather than Cells(al, 3)?

Yuenk · Dec 1, 2014

It doesnt work the way it should. It does delete the data in columns A & B but it will also delete every other work number. I have provided additional data so you can see. In the end this data Ijust provided shouldnt be deleted with the other revisions.

[TABLE="width: 2411"]
<TBODY>[TR]
[TD]Proper[/TD]
[TD]E2[/TD]
[TD]2195766[/TD]
[TD]DES[/TD]
[TD]E_EOSYSRF[/TD]
[TD]OH[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Working[/TD]
[TD]OP501[/TD]
[TD]Design[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1837005[/TD]
[TD]1837005[/TD]
[TD] [/TD]
[TD]28232[/TD]
[TD]598788[/TD]
[TD]Structure[/TD]
[TD]562 Tree St[/TD]
[TD]09/08/14[/TD]
[TD]09/08/14[/TD]
[TD] [/TD]
[TD]Name[/TD]
[TD]09/08/14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Proper[/TD]
[TD]E3[/TD]
[TD]2196032[/TD]
[TD]SCH[/TD]
[TD]E_EOSYSRF[/TD]
[TD]OH[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Approved[/TD]
[TD]OPV[/TD]
[TD]Installed[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1836360[/TD]
[TD]1836360[/TD]
[TD]09/24/14[/TD]
[TD]35705[/TD]
[TD]572102[/TD]
[TD]Structure[/TD]
[TD]563 Tree St[/TD]
[TD]09/24/14[/TD]
[TD]09/08/14[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]09/08/14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Proper[/TD]
[TD]E4[/TD]
[TD]2201405[/TD]
[TD]PCLOS[/TD]
[TD]E_EOSYSRF[/TD]
[TD]OH[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Approved[/TD]
[TD]OPV[/TD]
[TD]Cancelled[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD]I[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]1782146[/TD]
[TD]1782146[/TD]
[TD]10/21/14[/TD]
[TD]T45[/TD]
[TD]555468[/TD]
[TD]Structure[/TD]
[TD]564 Tree St[/TD]
[TD]10/22/14[/TD]
[TD]09/12/14[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]09/12/14[/TD]
[TD]Name[/TD]
[TD]09/12/14[/TD]
[TD]Name[/TD]
[TD]09/12/14[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Proper[/TD]
[TD]E5[/TD]
[TD]2212393[/TD]
[TD]PCLOS[/TD]
[TD]E_EOSYSRF[/TD]
[TD]OH[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Approved[/TD]
[TD]OPV[/TD]
[TD]Installed[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1692623[/TD]
[TD]1692623[/TD]
[TD]10/09/14[/TD]
[TD]65411[/TD]
[TD]581429[/TD]
[TD]Structure[/TD]
[TD]565 Tree St[/TD]
[TD]10/09/14[/TD]
[TD]09/22/14[/TD]
[TD] [/TD]
[TD]Name[/TD]
[TD]09/22/14[/TD]
[TD]Name[/TD]
[TD]09/22/14[/TD]
[TD]Name[/TD]
[TD]09/22/14[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Proper[/TD]
[TD]E6[/TD]
[TD]2225094[/TD]
[TD]SCH[/TD]
[TD]E_EOERF[/TD]
[TD]OH[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Approved[/TD]
[TD]OP452[/TD]
[TD]Proposed[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1858174[/TD]
[TD] [/TD]
[TD]20799[/TD]
[TD]587433[/TD]
[TD]Structure[/TD]
[TD]566 Tree St[/TD]
[TD]09/30/14[/TD]
[TD]09/30/14[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]09/30/14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Proper[/TD]
[TD]E7[/TD]
[TD]2226710[/TD]
[TD]WIP[/TD]
[TD]E_EOSYSRF[/TD]
[TD]OH[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Approved[/TD]
[TD]OP501[/TD]
[TD]Installed[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1855552[/TD]
[TD]1855552[/TD]
[TD]10/28/14[/TD]
[TD]9987[/TD]
[TD]568014[/TD]
[TD]Structure[/TD]
[TD]567 Tree St[/TD]
[TD]10/28/14[/TD]
[TD]10/01/14[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]10/01/14[/TD]
[TD]Name[/TD]
[TD]10/01/14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Proper[/TD]
[TD]E8[/TD]
[TD]2237856[/TD]
[TD]PCLOS[/TD]
[TD]E_EOERF[/TD]
[TD]OH[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Approved[/TD]
[TD]OP451[/TD]
[TD]Installed[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1860559[/TD]
[TD]1860559[/TD]
[TD]10/24/14[/TD]
[TD]58578[/TD]
[TD]560288[/TD]
[TD]Structure[/TD]
[TD]568 Tree St[/TD]
[TD]10/24/14[/TD]
[TD]10/10/14[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]10/10/14[/TD]
[TD]Name[/TD]
[TD]10/10/14[/TD]
[TD]Name[/TD]
[TD]10/10/14[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Proper[/TD]
[TD]E9[/TD]
[TD]2247070[/TD]
[TD]SCH[/TD]
[TD]E_EOSYSRF[/TD]
[TD]OH[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Approved[/TD]
[TD]OPV[/TD]
[TD]Proposed[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1692636[/TD]
[TD]1692636[/TD]
[TD] [/TD]
[TD]62080[/TD]
[TD]574826[/TD]
[TD]Structure[/TD]
[TD]569 Tree St[/TD]
[TD]11/05/14[/TD]
[TD]10/20/14[/TD]
[TD] [/TD]
[TD]Name[/TD]
[TD]10/20/14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Proper[/TD]
[TD]E10[/TD]
[TD]2255397[/TD]
[TD]SCH[/TD]
[TD]E_EOSYSRF[/TD]
[TD]OH[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Approved[/TD]
[TD]OPV[/TD]
[TD]Proposed[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1845100[/TD]
[TD]1845100[/TD]
[TD] [/TD]
[TD]14639[/TD]
[TD]631163[/TD]
[TD]Structure[/TD]
[TD]570 Tree St[/TD]
[TD]11/04/14[/TD]
[TD]10/27/14[/TD]
[TD] [/TD]
[TD]Name[/TD]
[TD]10/27/14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Proper[/TD]
[TD]E11[/TD]
[TD]2258056[/TD]
[TD]PCLOS[/TD]
[TD]E_EOERF[/TD]
[TD]OH[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Approved[/TD]
[TD]OP451[/TD]
[TD]Installed[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1873428[/TD]
[TD]1873428[/TD]
[TD]10/31/14[/TD]
[TD]71358[/TD]
[TD]575027[/TD]
[TD]Structure[/TD]
[TD]571 Tree St[/TD]
[TD]10/31/14[/TD]
[TD]10/29/14[/TD]
[TD] [/TD]
[TD]Name[/TD]
[TD]10/29/14[/TD]
[TD]Name[/TD]
[TD]10/29/14[/TD]
[TD]Name[/TD]
[TD]10/29/14[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Proper[/TD]
[TD]E12[/TD]
[TD]2258062[/TD]
[TD]SCH[/TD]
[TD]E_EOERF[/TD]
[TD]OH[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Approved[/TD]
[TD]OP451[/TD]
[TD]Installed[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1873411[/TD]
[TD]10/31/14[/TD]
[TD]71178[/TD]
[TD]582105[/TD]
[TD]Structure[/TD]
[TD]572 Tree St[/TD]
[TD]10/31/14[/TD]
[TD]10/29/14[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]10/29/14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Proper[/TD]
[TD]E13[/TD]
[TD]2261357[/TD]
[TD]SCH[/TD]
[TD]E_EOSYSRF[/TD]
[TD]OH[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Approved[/TD]
[TD]OPV[/TD]
[TD]Proposed[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1451019[/TD]
[TD]1451019[/TD]
[TD] [/TD]
[TD]37844[/TD]
[TD]561142[/TD]
[TD]Structure[/TD]
[TD]573 Tree St[/TD]
[TD]11/04/14[/TD]
[TD]10/31/14[/TD]
[TD] [/TD]
[TD]Name[/TD]
[TD]10/31/14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Proper[/TD]
[TD]E14[/TD]
[TD]2261915[/TD]
[TD]APPRV[/TD]
[TD]E_EOSYSRF[/TD]
[TD]OH[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]Y[/TD]
[TD]Official[/TD]
[TD]OPV[/TD]
[TD]Design[/TD]
[TD]I[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1709047[/TD]
[TD]1709047[/TD]
[TD] [/TD]
[TD]T74[/TD]
[TD]576194[/TD]
[TD]Structure[/TD]
[TD]574 Tree St[/TD]
[TD]11/05/14[/TD]
[TD]10/31/14[/TD]
[TD] [/TD]
[TD]Name[/TD]
[TD]10/31/14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL span=7><COL><COL span=11><COL><COL><COL><COL><COL span=9></COLGROUP>[/TABLE]

Andrew Poulsom · Dec 1, 2014

Please post a small but representative sample of your data before and after.

Yuenk · Dec 1, 2014

[TABLE="width: 617"]
<TBODY>[TR]
[TD]Proper/Error 
[/TD]
[TD]Local District
[/TD]
[TD]Work Number
[/TD]
[TD]Area
[/TD]
[TD]Version
[/TD]
[TD]Revision Status
[/TD]
[TD]Serial Number
[/TD]
[TD]Rank
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Working
[/TD]
[TD]591229
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]Working
[/TD]
[TD]591028
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]9
[/TD]
[TD]1
[/TD]
[TD]Working
[/TD]
[TD]589053
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]8
[/TD]
[TD]1
[/TD]
[TD]Working
[/TD]
[TD]642990
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[TD]Working
[/TD]
[TD]591028
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]8
[/TD]
[TD]2
[/TD]
[TD]Working
[/TD]
[TD]642990
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]9
[/TD]
[TD]2
[/TD]
[TD]Working
[/TD]
[TD]589053
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]Working
[/TD]
[TD]591028
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]Working
[/TD]
[TD]642990
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]6
[/TD]
[TD]3
[/TD]
[TD]Working
[/TD]
[TD]589053
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]5
[/TD]
[TD]4
[/TD]
[TD]Approved
[/TD]
[TD]591028
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]8
[/TD]
[TD]4
[/TD]
[TD]Approved
[/TD]
[TD]642990
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]2228124
[/TD]
[TD]9
[/TD]
[TD]4
[/TD]
[TD]Approved
[/TD]
[TD]589053
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E2
[/TD]
[TD]2195766
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]Working
[/TD]
[TD]598788
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E3
[/TD]
[TD]2196032
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Approved
[/TD]
[TD]572102
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E4
[/TD]
[TD]2201405
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Approved
[/TD]
[TD]555468
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E5
[/TD]
[TD]2212393
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Approved
[/TD]
[TD]581429
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E6
[/TD]
[TD]2225094
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Approved
[/TD]
[TD]587433
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E7
[/TD]
[TD]2226710
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Approved
[/TD]
[TD]568014
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E8
[/TD]
[TD]2237856
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Approved
[/TD]
[TD]560288
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E9
[/TD]
[TD]2247070
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Approved
[/TD]
[TD]574826
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E10
[/TD]
[TD]2255397
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Approved
[/TD]
[TD]631163
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E11
[/TD]
[TD]2258056
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Approved
[/TD]
[TD]575027
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E12
[/TD]
[TD]2258062
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Approved
[/TD]
[TD]582105
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E13
[/TD]
[TD]2261357
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Approved
[/TD]
[TD]561142
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E14
[/TD]
[TD]2261915
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]Official
[/TD]
[TD]576194
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
</TBODY>[/TABLE]

Same information. In this example I want to keep all the data except for Version 1,2 & 3 of Work Number 2228124

Andrew Poulsom · Dec 1, 2014

With this VBA code:

Code:

Sub keep_highest()
    Dim k As Object, a, al As Long
    Set k = CreateObject("Scripting.dictionary")
    a = Sheets("sheet1").Cells(1).CurrentRegion
    For al = 2 To UBound(a)
        If a(al, 8) > k(a(al, 3)) Then k(a(al, 3)) = a(al, 8)
    Next al
    For al = UBound(a) To 2 Step -1
        If a(al, 8) <> k(a(al, 3)) Then Cells(al, 1).Resize(, UBound(a, 2)).Delete xlUp
    Next al
End Sub

I get:

Excel 2010

A

B

C

D

E

F

G

H

1

Proper/Error

Local District

Work Number

Area

Version

Revision Status

Serial Number

Rank

2

Proper

E1

2228124

5

4

Approved

591028

3

Proper

E1

2228124

8

4

Approved

642990

3

4

Proper

E1

2228124

9

4

Approved

589053

3

5

Proper

E2

2195766

3

1

Working

598788

2

6

Proper

E3

2196032

1

Approved

572102

3

7

Proper

E4

2201405

1

Approved

555468

3

8

Proper

E5

2212393

1

Approved

581429

3

9

Proper

E6

2225094

1

Approved

587433

3

10

Proper

E7

2226710

1

Approved

568014

3

11

Proper

E8

2237856

1

Approved

560288

3

12

Proper

E9

2247070

1

Approved

574826

3

13

Proper

E10

2255397

1

Approved

631163

3

14

Proper

E11

2258056

1

Approved

575027

3

15

Proper

E12

2258062

1

Approved

582105

3

16

Proper

E13

2261357

1

Approved

561142

3

17

Proper

E14

2261915

1

2

Official

576194

4

Sheet1

Yuenk · Dec 1, 2014

Andrew this works perfectly now! thanks for all your help!

Andrew Poulsom · Dec 1, 2014

All I did was make the change I suggested in Post #4, but I'm glad it works for you.

Macro almost works. Doesn't delete as desired

Yuenk

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

Yuenk

New Member

Andrew Poulsom

MrExcel MVP

Yuenk

New Member

Andrew Poulsom

MrExcel MVP

Yuenk

New Member

Andrew Poulsom

MrExcel MVP

Yuenk

New Member

Andrew Poulsom

MrExcel MVP

Similar threads

Forum statistics

Share this page

Macro almost works. Doesn't delete as desired

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock