Delete rows that DO NOT contain criteria texts

[masterthetrade]

Hi

I am novice excel user and trying to search and found solution to my issue, I came close to it but not yet got what I wanted exactly. I am looking for a macro to delete rows that DO NOT contain criteria. But what I could achieve so far is deleting all rows except the first word ie ACC here. My code so far is as follows

Please Login or Register  to view this content.

I see some issue at code marked in red. Need help resolving this. Thanks, in advance

[:) Sixthsense :)]

I believe instead of mentioning the criteria's inside the code I suggest you to use a helper column in a separate worksheet and add all your criteria's and and use a loop on it.

[arlu1201]

masterthetrade,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

[arlu1201]

You have 2 options -

1. If the list of items to be deleted is huge, you can retain them in a column in your macro file and have the macro loop through each one.

2. If the list of items to be retained is small, you can mention those in the macro and ask the macro to check only the retained ones, everything else to be deleted.

[masterthetrade]

You have 2 options -

1. If the list of items to be deleted is huge, you can retain them in a column in your macro file and have the macro loop through each one.

2. If the list of items to be retained is small, you can mention those in the macro and ask the macro to check only the retained ones, everything else to be deleted.

Hi Arlu 1201

Thankyou. Rows to be deleted are about 1500 and rows to be retained are 50 that contain words I mentioned in the code I posted. Is it possible to modify the code I posted and re-post the modified code here? As I mentioned earlier, I am novice to VB and do not know much of it. I searched the forum and found the above code and slightly modified with little knowledge I have but it is not enough to resolve the issue! Thank you. Please note the 50 words I mentioned are pertaining to 50 rows in column A, they are not duplicated in any other row or column.

[arlu1201]

Try this code - Sheet1 contains the entire data in column A, Sheet2 contains the list of items to be deleted in column A.

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[masterthetrade]

Hi Arlu1201

This has done the trick for me, thank you very much

One more small request, in continuation to above. After deleting rows using the above code, if I wanted to few more columns also to be deleted, which have headers as SERIES, ISIN and LAST; how do I do that? If you do not mind can you please paste the complete consolidated code?

Thank you.

[arlu1201]

Are these columns in specific locations every time you get the data? For e.g. if SERIES is in column A, will it always be column A?

Are these the only 3 columns to be deleted?

[masterthetrade]

Are these columns in specific locations every time you get the data? For e.g. if SERIES is in column A, will it always be column A?

Are these the only 3 columns to be deleted?

Apart from the rows mentioned in the previous code the following columns have to be deleted. The rows and columns to be deleted are almost the same every day.

SERIES (Column B), LAST (Column G), PREVCLOSE (Column H), TOTTRDQTY (Columns I), TOTTRDVAL (Column J), TOTALTRADES (Column L), ISIN (Column M) are to be deleted. Column headers remain the same every day, only cell values change.

Thank you.

[arlu1201]

Updated code -

Please Login or Register  to view this content.

[masterthetrade]

Hi Arlette

I tried the above code but it is deleting column K but not deleting Columns L & M. ie deleting Column B and Columns G:K but not deleting Columns L & M

[arlu1201]

Oops actually these are delete codes, hence you will need to reverse it.

Updated code -

Please Login or Register  to view this content.

[masterthetrade]

yaaahoo...! This works fine, Thank you.