Delete / Keep rows containing specific text.

[fluteloop]

Hello

I need some help to remove/keep rows from a large spreadsheet. I think the easiest way would be to specify the text in the row I want to keep.
The text which requires sorting is in one column A: and looks like this -

ARM0009
ARM0041
BOR0024
BOR0035
CEN0029
CHS0003

for the above I would like to retain rows which contain BOR and CEN and delete all the others.

I have tried numerous macros but being new to it I'm struggling to get anything to work.

Any help would be much appreciated.

Thanks

[martindwilson]

there are several ways of doing this, depends on what all your data looks like.you can simply filter on does not begin with bor or cen and delete the visible rows
or if there are more conditions an advanced filter or even a function to identify which rows to discard /keep
or since this is the programming sub forum perhaps some vba to automate the whole thing but thats probably overkill lol

[fluteloop]

Hi Martin thanks for the response.
The filter seems a good idea although I'm dealing with this data daily an to have an automated process would be excellent.
I did have a go with a few scripts already on here but failed to have any luck.

Some help on how to get the script going would be great or any other suggestions welcome.

Thanks

[nilem]

try so

Please Login or Register  to view this content.

It is assumed that A1 is the column header.

[fluteloop]

Thanks Nilem that worked great.
How do I go about adding in more arguments to the filter command?
I tried expanding on BOR and CEN with 6 others (TYR, TYN, LOT) following the same command but it gave an error of Wrong Number of Arguments or Invalid Property Assignment.

Thanks

[nilem]

Try different options

Please Login or Register  to view this content.

oops these options are not working correctly

[fluteloop]

Thanks Nilem. I was having problems with it as well.

I have attached a simple test file which will give you an idea of what I'm trying to achieve.

Thanks

Gary

[nilem]

Hi, Gary. Let's try without a filter.

Please Login or Register  to view this content.

[snb]

Please Login or Register  to view this content.

[fluteloop]

Thanks Nilem, Got it sorted!! I was looking at the wrong line!!!

This is a great help and very much appreciated.

Regards

[nilem]

highlighting - it was just for testing

Please Login or Register  to view this content.

[fluteloop]

Thanks, Took me a bit of time to notice!!!

Thanks again.

[pedrohcosta]

I know this thread is old but I have a similar problem.

I used the macros provided here to sort some rows successfully but I need a certain row and the following one. My data looks somewhat like this:

BRBRBRBR
1234

BRBRBRBR
4567

BRBRBRBR
7890

and I need every row with BR plus the one that comes after it.

Thanks in advance for any kind of assistance.

[protonLeah]

pedrohcost,
I know this thread is old but ... (I don't care...)
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.