Delete Rows based on criteria.

Hello everyone,

I had a raw data sheet in which i need to prepare a statement just like the attached worksheet.

My requirement is to delete all those rows in the department column except the department which starts with "C" Just like "CNN" & "CNN-IN".

Rows with data containing the words starting with "CNN" should not be deleted .The rows can contain words with "CNN" or "CNN*"(here * denotes anything after the word CNN)

I had just formatted the whole worksheet for easy reference.Actually the raw data is extracted from other program which is very clumsy & irregular.

The department column might be in any column.
Please tell me the easy way out.

the sheet seems to be sorted by column A. If yo want to retain this sort order:

sort by department
delete rows before CNN entries
delete rows after CNN entries
sort by column A

Hi teylyn, thanks for the fast reply.Yes, as i said earlier the whole data is extracted from other program.What i want is to delete only those rows which dont contain the words Starting with "CNN" in the department column & the department column will be any where to the right side of the AMOUNT column.

I need to delete all those unncessary Rows & columns in between those of Amount & department column and also delete the rows which dont start with the department name "CNN"
Hope i made it clear

Hi excelkeechak
It would help me if you would post a copy of your extracted raw data. The data you posted has been

formatted the whole worksheet for easy reference

I'd like to see the data before it's been formatted so I can see what the options are.
John

I need to delete all those unncessary Rows & columns in between those of Amount & department column and also delete the rows which dont start with the department name "CNN"
Hope i made it clear

Afraid not. What do you mean by "rows & columns in between those of amount & department"? There is no column between Amount and department in your example.

My suggestions shows you a way to quickly delete all rows that do not start with "CNN" in the column labeled department. If that is not what you require, please give a better example.

You may want to mock up a "before" and "after" sheet to illustrate your point.

Due to integrity reasons i cannot post it.Sorry for that.
Let me know the code if there is any possibility of copying all the rows which contain the word CNN & CNN*.* in the department column and paste it into a new worksheet.

Hmmm.Ok.based on my original data.i m trying to give a sample data.my Original data looks similar to the sample data i had attached.what i want is to find and copy all those rows to a new worksheet whose departments are "CNN" or which start with "C*"(Just Like CNN-IN).

I had uploaded a new example for reference.Kindly help.

Hi excelkeechak
I may be able to help you with this, however, your "Raw Data" worksheet seems to bear no relationship to your "req format" worksheet. Things don't appear to add up?
I'll take a stab at it but it may not be what you're looking for.
John

Try this on a copy of that sheet you uploaded in post #7. It will format that sheet to the specified layout.

Please Login or Register  to view this content.

Yes, Exactly this is what i needed.Thanks Very Much JBeaucaire for the code.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

I had one more requirement please,what would be the generalized code for the same scenario just like

Please Login or Register  to view this content.

to remove unwanted rows or columns which are different from the example.

This is the lines of code that are filtering data based on cell content, the code you cited above is removing the empty rows/columns.

Please Login or Register  to view this content.

I've highlighted the part you need to edit. Currently it is filtering to show rows that do not start with the letter "c". The next line of code deletes them, the third turns the filter back off to reveal the ones left behind, the ones that do start with the letter "c". Based on your question, the code originally given does remove items that do not match.

So, you can edit that part in red to cause different results. Perhaps:

"=c*" - only show cells starting with "c"

If that's not it, just play with it.

Thanks JBeaucaire for taking your time in explaining the code.

Glad to help!

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)