I have a user form that is populated from a workbook that lists informaion on
various projects. The user selects the project they wish to update via a
combo box. Once the user updates the project and presses save, two copies of
the project appear on the worksheet with the oldest copy on top. I
automatically sort the project to ensure both copies of the project are
showing together. What i want to do, via VBA code, is if, after sorting,
there are two rows of project information together (match found) then delete
the first instance of the project. For example, project 10 is titled Civil
Engineering. My user selects Civil Engineering, updates various start and
end date information, or generic project information and presses SAVE. Once
SAVE button is pressed, the worksheet is sorted and both copies of Civil
Engineering appear one above the other, with the newest second. I want, if
there are two copies of a project, the oldest (lowested numbered row) to
delete. Can Anyone Help

I didn't test the code below but it should work. The code looks for the
project ID in Column A and if two rows have the same ID then the one with the
lowest row number gets deleted. I added a feature that will look for
multiple duplicates which means the code will take a little longer to run.
If you have a small number of rows then this shouldn't be a problem. If you
have over 10,000 rows then we may want to modify the code to run quicker and
only look for one duplicate.

Sub FindDuplicates()
'code will find multiple duplicates and delete all duplicates
'duplicatte with lowest row number will be deleted
RowCount = 1
Do While Range("A" & RowCount) < ""
ProjID = Range("A" & RowCount)
'look for duplicate
DupRowCount = RowCount + 1
Do While Range("A" & DupRowCount) < ""
DupProjID = Range("A" & DupRowCount)
If ProjID = DupProjID Then
Rows(RowCount).Delete
ProjID = Range("A" & RowCount)
DupRowCount = RowCount + 1
Else
DupRowCount = DupRowCount + 1
End If
Loop
RowCount = RowCount + 1
Loop
End Sub


"Gnerks" wrote:

I have a user form that is populated from a workbook that lists informaion on
various projects. The user selects the project they wish to update via a
combo box. Once the user updates the project and presses save, two copies of
the project appear on the worksheet with the oldest copy on top. I
automatically sort the project to ensure both copies of the project are
showing together. What i want to do, via VBA code, is if, after sorting,
there are two rows of project information together (match found) then delete
the first instance of the project. For example, project 10 is titled Civil
Engineering. My user selects Civil Engineering, updates various start and
end date information, or generic project information and presses SAVE. Once
SAVE button is pressed, the worksheet is sorted and both copies of Civil
Engineering appear one above the other, with the newest second. I want, if
there are two copies of a project, the oldest (lowested numbered row) to
delete. Can Anyone Help

Joel - thank you, works like a charm and does exactly what I needed.

"Joel" wrote:

I didn't test the code below but it should work. The code looks for the
project ID in Column A and if two rows have the same ID then the one with the
lowest row number gets deleted. I added a feature that will look for
multiple duplicates which means the code will take a little longer to run.
If you have a small number of rows then this shouldn't be a problem. If you
have over 10,000 rows then we may want to modify the code to run quicker and
only look for one duplicate.

Sub FindDuplicates()
'code will find multiple duplicates and delete all duplicates
'duplicatte with lowest row number will be deleted
RowCount = 1
Do While Range("A" & RowCount) < ""
ProjID = Range("A" & RowCount)
'look for duplicate
DupRowCount = RowCount + 1
Do While Range("A" & DupRowCount) < ""
DupProjID = Range("A" & DupRowCount)
If ProjID = DupProjID Then
Rows(RowCount).Delete
ProjID = Range("A" & RowCount)
DupRowCount = RowCount + 1
Else
DupRowCount = DupRowCount + 1
End If
Loop
RowCount = RowCount + 1
Loop
End Sub


"Gnerks" wrote:

I have a user form that is populated from a workbook that lists informaion on
various projects. The user selects the project they wish to update via a
combo box. Once the user updates the project and presses save, two copies of
the project appear on the worksheet with the oldest copy on top. I
automatically sort the project to ensure both copies of the project are
showing together. What i want to do, via VBA code, is if, after sorting,
there are two rows of project information together (match found) then delete
the first instance of the project. For example, project 10 is titled Civil
Engineering. My user selects Civil Engineering, updates various start and
end date information, or generic project information and presses SAVE. Once
SAVE button is pressed, the worksheet is sorted and both copies of Civil
Engineering appear one above the other, with the newest second. I want, if
there are two copies of a project, the oldest (lowested numbered row) to
delete. Can Anyone Help