Hey all...
OK, so here is my issue. I want to write a macro (and have it available in any Excel file I open) that will "walk" row-by-row through a XL sheet, checking the .Value of the cell in Column 9 and if one of several text strings is found in that cell, delete the entire row. (This is to process/cleanup a virus report generated by our anti-virus monitoring server, basically I want to get rid of any entries where the virus was cleaned or Quarantined, or if the entry is a duplicate of the previous line).
The columns/sample data are labeled as such:
Computer name Scan Type Date Time Login name Infected file Directory Virus name Action
GEMELO1C Real-time Scan 20040206 204112 GEMELO1 i.exe c:\ TROJ_SMALL.AC Virus successfully detected, cannot perform the Clean action (Virus successfully detected, cannot perform the Quarantine action)
GPDELL-1 Real-time Scan 20040203 83959 GPDELL x.exe C:\ TROJ_SMALL.AR Virus successfully detected, cannot perform the Clean action (Quarantine)
(Hopefully the formatting isn't too screwed up on that)
The Action column (9) is the one I'm interested in here.
The code I have is this:
Sub CleanTrendReport()
rowx = 1 ' Start in the first row
col = 9 ' The column to look for duplicates in the sheet
Const sQuarantine = "Virus successfully detected, cannot perform the Clean action (Quarantine)"
Const sClean = "Clean"
Application.ScreenUpdating
= False
Do Until Cells(rowx + 1, col).Value = "" ' Loop until end of data
If Cells(rowx, col).Value = Cells(rowx + 1, col).Value _
Or Cells(rowx, col).Value = sClean Or Cells(rowx, col).Value = sQuarantine Then
' MsgBox "Column I value at row " & rowx & " is: " & Cells(rowx, col).Value
Cells(rowx + 1, col).EntireRow.Delete
Else
rowx = rowx + 1 ' Increment to next row
End If
Loop
Beep
Application.ScreenUpdating
= True
End Sub
-------------------
If I remove the comment on the MsgBox function above, it seems like it gets stuck there after the Else statement, and will keep popping up showing the same row; for some reason rowx is never incremented. Does anyone see the error of my ways? I'm definitely a beginning VBA programmer, but have a good deal of programming experience in other languages.
Thanks.
dogztar
