VBA Question - Delete set if any member meets criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

porter444 - 13 Sep 2007 21:28 GMT

I have a worksheet that contains student enrollment information in courses
and in some cases the same student has multiple items listed for the same
course.

The logic is as follows:

IF the student ID AND the course ID are the same the are considered a SET.
IF any member of the SET has a status = Completed, DELETE the SET.

Here is an example of the "before" data: (comma seperated)

Student ID,Course ID,Status
1,QUA100,Completed
1,QUA100,Enrolled
1,QUA100,No Show
1,QUA100,No Show
1,AO100,Enrolled
1,AO100,No Show
1,AO100,No Show
2,QUA100,Completed
3,QUA100,Enrolled
3,QUA100,No Show

Here is an example of the "after" data: (comma seperated)

Student ID,Course ID,Status
1,AO100,Enrolled
1,AO100,No Show
1,AO100,No Show
3,QUA100,Enrolled
3,QUA100,No Show

Thanks in advance!

Scott

Reply to this Message

Joel - 14 Sep 2007 02:10 GMT

Try this code. It was simple.

Sub deletedups()

StartRow = 2
RowCount = 2
Completed = False
Do While Cells(RowCount, "A") <> ""
If Cells(RowCount, "C") = "Completed" Then
Completed = True
End If
If (Cells(RowCount, "A").Value = _
Cells(RowCount + 1, "A").Value) And _
(Cells(RowCount, "B").Value = _
Cells(RowCount + 1, "B").Value) Then

RowCount = RowCount + 1
Else
If Completed = True Then
Rows(StartRow & ":" & _
RowCount).Delete
RowCount = StartRow
Completed = False
Else
RowCount = RowCount + 1
StartRow = RowCount
End If
End If
Loop
End Sub

> I have a worksheet that contains student enrollment information in courses
> and in some cases the same student has multiple items listed for the same
[quoted text clipped - 31 lines]
>
> Scott

Reply to this Message

Bill Renaud - 14 Sep 2007 04:59 GMT

Works fine, as long as the data is sorted by Student ID, and Course ID.
Probably should include code to sort the list first, just to make sure.

Signature

Regards,
Bill Renaud

Reply to this Message

porter444 - 14 Sep 2007 10:32 GMT

Thanks!

Reply to this Message