Need macro to delete entries

Is it possible to have a macro that deletes entries based on different colored text or keywords, below is a document of some attack logs from a game and we were trying to filter out the unwanted ones (logs are on sheet 2 so to not clutter up the page, and members on sheet 1) using excel 2007. Basically the only good entries we would like to keep are in green where it says something like 10:42:43 PM H00D hospitalized ZetaXeno 06/09/11

If its easier to look for the things that we dont want would be any lines in red heres a few samples but the color in general is unfavorable results
10:19:34 PM T-Bohn hospitalized crunkenstein 06/09/11
10:44:38 PM The_Ranger attacked Juan_Herndez and lost 06/09/11
any lines in green that contain attacked or mugged such as
10:44:38 PM The_Ranger attacked Juan_Herndez and lost 06/09/11
10:20:33 PM H00D mugged Gamer 06/09/11

The excel workbook posted is just a small sample of these we normally have 500-600+ entries in a day and thats to many to hand sort. The plan is to make an if statement or something like that, so that every time it seen H00d hospitalized in green it would add 1 to his total. Not sure how to do that part yet, tried if statements with =if * H00d hospitalized*, then if true count++ else blank but never got the logical part right.

Also not sure how hard it would be to strip the time and date in the macro but its not needed at all just in the other cell when you copy from the webpage.

Hello daniel0823,

Welcome to the Forum!

The attached workbook will increment the point count for members if words "attacked" or "mugged" is found in the log. The time and date will also be removed.

The macro ignores names found in the log that are not found in Members. However, they will be listed on log sheet. You didn't say if this condition would be problem or not. Let me know if want to handle this differently and I will correct it.

Here are the two macros. Currently, you will need to manually run the macro "DeleteRows". It could be automated, but I would needed to know what would trigger it.

Module1 Macros

Please Login or Register  to view this content.

Thank you very much for that Leith Ross, needed it to only add the green hospitalized those are the members attacks won, the red hospitalizations are enemies, i tried to edit the macro and change attacked and mugged to hospitalized but when i ran the script it would delete the first letter of every members name and not add to the count. Not sure what i did to cause this, just changed attacked|mugged to hospitalized, and edited the comments as well to reflect this change. Was running macros in order they appeared in the list so delete row first then load member 2nd

included it as an attachment to see

Hello daniel0823,

The problem was with the time. I had written the code (foolishly) to expect 2 digits for hours, minutes, and seconds. By replacing the MID function with a Regular Expression, the macro is now more robust. Changes in the digits of either the time or date won't interfere with parsing the text between the two. Here is the updated macro. It has been installed in the attached workbook.

Please Login or Register  to view this content.

If you are happy with the results and your question has been answered, please mark your thread as solved.

Hello daniel0823,

I am not sure I follow what your saying. Can you post the workbook so I can follow along with what you're saying?

Hello daniel0823,

I updated the macro to check for both the time and date being present before extracting the member's name from the cell and updating the point count.

Please Login or Register  to view this content.

Hello Daniel,

If you don't care if the date and time is there or not, I need to know what the rules are to apply. Once you tell me what rules to apply to the time, date, and remaining text in between, we can move forward.

Not sure how to clearly explain it for you. I guess if you made another rule that removed time from an entry if it lacks a date at the end of the entry would solve the problem


In the last one i uploaded when you run it you get all the correct results which would be a name hospitalized name. you also get a few entries these below. I looked in log and its because these entries appear exactly like that, theres 25 of them on the page when i copy them.

When i paste them into excel it takes the first date and pastes it in its own cell. Then every date after gets stuck on the end of the next entry. So the 25th entry is left without a date at the end, and when you run the macro its leaving the time infront of the name, causing the hit counter to not increase on any of the entries below.

Examples of entries after running the current macro that arent added to their totals

Please Login or Register  to view this content.

The text i do want is in the middle of the entrys like below. Need the name hospitalized name
Hub_Master hospitalized RaReLy

Hello Daniel,

It looks to me like the time and date are superfluous entities. The macro should check if they exist and if so remove them. Does that match with what you need?

Hello Daniel,

Here is the revised macro which will remove the time and date if they are present to extract the member's name. If neither are present the member's name will still be extracted correctly. Here is the updated macro and workbook.

Please Login or Register  to view this content.