Max Value

dtopinka

New Member
Joined
Nov 2, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,821
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this VBA Code:
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

New Member
Joined
Nov 2, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
thank you maabadi.. i figured out how to eliminate the min seq numbers using a sql query in ms access.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,821
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & Glad you can do it.
 

dtopinka

New Member
Joined
Nov 2, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Try this VBA Code:
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

New Member
Joined
Nov 2, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
i dont have office 365 therefor the maxifs won't work. any other suggestions?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,821
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

dtopinka

New Member
Joined
Nov 2, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
Try this:
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,695
Office Version
  1. 365
Platform
  1. Windows
As you are using 2007, you won't have the aggregate function as it came out with the 2010 version.
 

dtopinka

New Member
Joined
Nov 2, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,129,843
Messages
5,638,677
Members
417,041
Latest member
Molo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top