Help with Macro
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. |
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. |
All times are GMT +1. The time now is 06:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com