Macro to Remove text based on specific parameters

**jhc8255** · 09-02-2010, 08:51 PM

Hi guys,

I have an excel sheet file that looks like the following

===begin===
Message#: 1
From: abc
BCC:111
test
test

test

===end===
===begin===
Message#: 2
From: xyz
BCC:112
test
test

test
===end===
===begin===
Message#: 3
From: abc
BCC:112
test
test

test
===end===
===begin===
Message#: 4
From: xyz
BCC:114
test
test

test
===end===

I would like to take out all text in between "===begin===" and "===end===" in which the "From" part is not from abc. So the output file would look like the following:

===begin===
Message#: 1
From: abc
BCC:111
test
test

test

===end===
===begin===
Message#: 3
From: abc
BCC:112
test
test

test
===end===

I've thought through this a lot but can not seem to come up with the right solution. Could you guys help me out? It's really sad that I realize the capabilities of excel vb but I'm so helpless when it comes to writing such macro

Thanks guys!

**stanleydgromjr** · 09-02-2010, 10:30 PM

jhc8255,

Welcome to the Excel Forum.

I assume that your raw data is in Sheet1, beginning in cell A1.

The macro creates a worksheet ABC, and copies all groups for From: abc.

Detach/open workbook jhc8255 - EF744304 - VP - SDG10.xls and run macro Getabc.

**jhc8255** · 09-03-2010, 12:49 AM

Stan,

All I can say is thank you! I wished I had your ability to write macros like you. I get errors all the time and when debugging I have hard time understanding what the problem even is. Anyways, the macro works perfect. Cheers mate!

Rgds,
james

P.S. to anyone out there... I think I might get little greedy on this and see if we can add another parameter to extract only those that have the word "test" within the begin and end lines (that is along with "From:abc" parameter). Would someone please help me on this based on Stan's macro? Thanks!

**stanleydgromjr** · 09-03-2010, 04:33 PM

jhc8255,

Thank you very much.

I assume that your raw data is in Sheet1, beginning in cell A1.

The updated macro creates a worksheet Results, and copies all groups for From: abc, and all groups where From:.... is not abc, but the group contains test.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Then run the "Getabctest" macro.

**stanleydgromjr** · 09-03-2010, 04:35 PM

jhc8255,

All I can say is thank you! I wished I had your ability to write macros like you. I get errors all the time and when debugging I have hard time understanding what the problem even is.