Excel Macro VBA code needed

[Zaks05]

Hi friends,

I would like to create a macro which deletes the records from the excel file for those values of the coulmn 'A' or first column for which the field values are other than Project, Client and Consulting.
My file looks like this:
A B c D
1. Project xyz Oracle 200
2. Div abc SQL 100
3. Client pqr ABC
4. sam def abc
5. Project abc SQL
6. Consulting abc SQL
7. Radiant abc SQL
9. Project abc SQL
10 Client Abc SQL

Now my requirement is I need to eliminate or delete those lines from my excel sheet where the value of my first column is other than Project or client or consulting.

Hence I need only lines 1,3,5,6,9 & 10. I want to delete rest of the lines.

Kindly help me out in giving the VBA code which does this job in macro.

Thanks & Regards,
Zaks.

 

[DPlank]

First of all, you should actually give it a try yourself - this isn't a helpdesk. While members are happy to help out if someone's stuck, we prefer to see someone trying to learn in the first place!

With that in mind I'd suggest recording what you want to do with the macro recorder, and look at the VBA code that gets generated. Once you see how it works, use the Help system to learn about looping so you can go over each row and decide if it needs deleted.

You'll learn quicker through application than us giving you the answers.

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[kphu]

Zak,

An easy approach using the macro recorder is to create a 5th column F and insert an if then else statement to find column A with values of "Project" or " and then return 0 and 1 for anything else example. =if(or(A1="Project",a1="Client", a1="Consulting"),0,1). Copy the formula down each row in column F. From there sort by column F so that all of the nonwanted rows are lumped together and delete those rows.

Record the macro and see if you can figure out how to change it. We'll help you with the coding if you're stuck.

Good luck

ken