Help with Macro
This should work. Once cells are in order, if two consecutive row have the
same part number the 1st row can be deleted because the second row will have
a higher operation number
Sub DeleteRows()
Const PartNumCol = "A"
Const OperationCol = "B"
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Set SortRange = Range(Cells(1, 1), Cells(LastRow, LastColumn))
SortRange.Sort _
Key1:=Range(PartNumCol & 1), _
Order1:=xlAscending, _
Key2:=Range(OperationCol & 1), _
Order2:=xlAscending
RowCount = 1
Do While Not IsEmpty(Range(PartNumCol & RowCount))
If Range(PartNumCol & RowCount) = _
Range(PartNumCol & (RowCount + 1)) Then
Range(PartNumCol & RowCount).EntireRow.Delete
Else
RowCount = RowCount + 1
End If
Loop
End Sub
"Cam" wrote:
Hello,
I have a table with thousand of row of data. The column consists of part#
and operation, etc....
Within the data rows, there are multiple rows with same part#, but at a
different operations in numerical 10 to 999. I would like to create a macro
of some sort to look in all the data, sort the data by part# column, then by
operation, then find part# with multiple operations, keep the latest
operation (highest of 10-999) and delete other lower operations.
Thank for any help.
|