Automatic delete row in Excel 2003

[Marc Robinson]

Hello,

I have a workbook full of data (Incident data reported to my helpdesk) that
is sorted into columns as follows:
Incident ID
Modified By
Contact Name
Last Modified
Current Status
Title

Duplicate data is produced in the workbook if the incident data had a change
of status in its lifecycle (Eg. from Open to Resolved to Closed). I want to
know how I can automatically delete rows of data if one particular row has
the word, 'Closed' against it?
Eg.
If Row 1 had an Incident number of 1 and Row 2 had an incident number of 1
and the corresponding Status had a value of Open in Row 1 and a value of
Closed in Row 2, how can i automcatically delete both rows?

Please contact me if more information is required.

Thanks,

Marc

 

[Per Jessen]

Hi Marc

This macro will do what you need. Just change IdCol, StatusCol and StartRow
as required.

Sub DeleteClosedCases()
Dim IdCol As String
Dim StatusCol As String
Dim StartRow As Integer
Dim StatusRange As Range
Dim tRow As Double
Dim FoundID
Dim ID As Double

IdCol = "A"
StatusCol = "B"
StartRow = 2 'Headings in Row 1
Set StatusRange = Range(Cells(StartRow, StatusCol), _
Cells(StartRow, StatusCol).End(xlDown))

For Each r In StatusRange
If r.Value = "Closed" Then
tRow = r.Row
ID = Cells(tRow, IdCol).Value
Set FoundID = Columns(IdCol).Find(What:=ID, After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlWhole)
Do Until FoundID Is Nothing
FoundID.EntireRow.Delete
Set FoundID = Columns(IdCol).Find(What:=ID, After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlWhole)
Loop
End If
Next
End Sub

Regards,
Per