Max Value

[dtopinka]

Column D has many work order numbers. Column L has sequence numbers. I would like vb code that will remove all rows except the ones with the highest sequence numbers. in the example sheet i would like to keep rows 6 and 7, delete all others. This is in a table and i would prefer the code to use header names as variables if possible. thank you in advance for any help you may provide.

below is a link to the spreadsheet.

Mr Excel - Google Drive

 

[maabadi]

Try this VBA Code:

Sub DeleteExMax()
Dim i As Long, Lr As Long, M As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
For i = Lr To 2 Step -1
M = Application.WorksheetFunction.MaxIfs(Range("L2:L" & Lr), Range("D2:D" & Lr), Range("D" & i))
If Range("L" & i) < M Then
Rows(i).Delete
End If
Next i

 

[dtopinka]

thank you maabadi.. i figured out how to eliminate the min seq numbers using a sql query in ms access.

 

[maabadi]

You're Welcome & Glad you can do it.

 

[dtopinka]

Try this VBA Code:

Sub DeleteExMax()
Dim i As Long, Lr As Long, M As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
For i = Lr To 2 Step -1
M = Application.WorksheetFunction.MaxIfs(Range("L2:L" & Lr), Range("D2:D" & Lr), Range("D" & i))
If Range("L" & i) < M Then
Rows(i).Delete
End If
Next i

the sql code didnt work. i tried this and i am getting a 438 error.

 

[dtopinka]

i dont have office 365 therefor the maxifs won't work. any other suggestions?

 

[maabadi]

Try this:

M = Application.WorksheetFunction.Aggregate(14, 6, Range("L2:L" & Lr) / (Range("D2:D" & Lr) = Range("D" & i)), 1)

 

[dtopinka]

Try this:

M = Application.WorksheetFunction.Aggregate(14, 6, Range("L2:L" & Lr) / (Range("D2:D" & Lr) = Range("D" & i)), 1)

Runtime error 13

The debug highlights this line.
M = Application.WorksheetFunction.Aggregate(14, 6, Range("L2:L" & Lr) / (Range("D2:D" & Lr) = Range("D" & i)), 1)
M is returning zero when i hover over it.

Also, what do the 14 and 6 represent in that line of code?

 

[Fluff]

As you are using 2007, you won't have the aggregate function as it came out with the 2010 version.

 

[dtopinka]

As you are using 2007, you won't have the aggregate function as it came out with the 2010 version.

2013 Pro Plus..

I googled what the aggregate numbers mean...