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
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
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
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
If the value of the cell is either "Clean" or "Virus successfully detected, cannot perform the Clean action (Quarantine)", you will never get to the next row. The program will always delete the row after it, but not the row w/either of those values.
Instead of
Cells(rowx + 1, col).EntireRow.Delete
try
Cells(rowx, col).EntireRow.Delete
I'm assuming you want to delete the row that says Clean or Quarantine.
dogztar,
When looping through rows to delete some, the trick is to move from the bottom
of the list and upward. That is what the procedure below does, which is a modified
version of the one you posted.
Sub CleanTrendReport()
'Declare variables
Dim r As Long 'Current row number
Dim x As String 'Value from cell
'Set constants
Const c = 9 'The column to check for duplicates in the sheet
Const sQuarantine = "Virus successfully detected, cannot perform the Clean action (Quarantine)"
Const sClean = "Clean"
'Turn off screen updating
Application.ScreenUpdating
'Find last row number
r = Cells(65536, col).End(xlUp).Row
Do Until r <= 1
'Read value into variable
x = Cells(r, c).Value
'Check value and delete row
If x = Cells(r + 1, c).Value Or x = sClean Or x = sQuarantine Then Rows(r).Delete
'Decrease row value
r = r - 1
Loop
'Make noise and turn screen updating back on
Beep
Application.ScreenUpdating
End Sub
/Ture
Sorry... here's one that works:
Sub CleanTrendReport()
'Declare variables
Dim r As Long 'Current row number
Dim x As String 'Value from cell
'Set constants
Const c = 9 'The column to check for duplicates in the sheet
Const sQuarantine = "Virus successfully detected, cannot perform the Clean action (Quarantine)"
Const sClean = "Clean"
'Turn off screen updating
Application.ScreenUpdating
'Find last row number
r = Cells(65536, c).End(xlUp).Row
Do Until r <= 1
'Read value into variable
x = Cells(r, c).Value
'Check value and delete row
If x = Cells(r + 1, c).Value Or x = sClean Or x = sQuarantine Then Rows(r).Delete
'Decrease row value
r = r - 1
Loop
'Make noise and turn screen updating back on
Beep
Application.ScreenUpdating
End Sub
/Ture
ture:
I'll give it a shot tomorrow. I finally got it working to some degree on my own last night, but it was deleting every line for some reason, but would only loop through 2 or 3 times (I had a msgbox in there to tell me what row it was on, etc). Also, using the EntireRow.Delete seems to be a problem, as when you do that there is no need to increment the row counter since you've moved to the next row anyway. I'll try the "bottom up" approach.
Thanks to you and everyone else who's commented.
dogztar
The new Q is at: http:Q_20881026.html
Business Accounts
Answer for Membership
by: CallandorPosted on 2004-02-10 at 12:08:30ID: 10325683
dogztar, it seems to work for me. Maybe you want to display Cells(rowx+1,col).Value instead of Cells(rowx,col)? It's a lot of OKs to hit, but it gets to the end.