Copy Row to Separate Sheet if Cell Contains Certain Text

[jsolder]

Hi
I've been trying very unsuccessfully to write some existing code which will copy rows of information if a specific cell in that row contains some specific characters. If a cell in column E contains the "P1" anywhere in a string of text I need to copy the whole row to another sheet. Happy to share the code I have or an example sheet if people think it will be useful.
Thanks in advance.
Jay

[kev_]

One way
Run from sheet1 in attached workbook with {CTRL} + t
Results put in sheet named "Results"

Please Login or Register  to view this content.

You could also put this formula into one of the columns (see attached workbook column G) and then filter on that column - all values > 0 include P1 in column E
Put formula in cell G2 and copy down

Please Login or Register  to view this content.

[jsolder]

Thanks Kev. What part of the code would I need to modify if I wanted it to copy everything up to column AN rather than column G? I'm just trying to understand what each part of the code is doing. Thanks, Jay

[kev_]

You are welcome

You do not need to alter anything

The code works by hiding rows that do not meet the criteria with:

Please Login or Register  to view this content.

Then it copies all the non-hidden cells in the sheet with:

Please Login or Register  to view this content.

It makes no difference how wide the row is.

[jsolder]

Oh great, that's brilliant then!

[jsolder]

Sorry Kev, I think I spoke too soon! Everytime I try and run the Macro with my data set I get the following error: "Excel cannot complete this task with available resources. Choose less data or close other applications.", the i get a run-time error '1004' Copy method of Range class failed. When i click on debug it highlights this line of code:

Please Login or Register  to view this content.

Any ideas...?!

[kev_]

One thing for you to note:

Everything relies on this line

Please Login or Register  to view this content.

VBA looks right and down from A1 until it finds an empty column in one direction and an empty row in the other to define the "CurrentRegion" range


It means that your headings must be in row 1, that your data starts in row 2, that there are no blank columns or blank rows in the data (ie data must be contiguous)

[jsolder]

Is there an alternative way of defining the "CurrentRegion" range, as there will be some blank columns within the data, however there will never be a blank row until the end of the data set.

[jindon]

Happy to share the code I have or an example sheet if people think it will be useful.
Thanks in advance.
Jay

Yes, it will be and I feel Filter would be the best method in this case.

[jsolder]

Hi, see example attached

[kev_]

Run the macro in the attached workbook with {CTRL} + t

As requested range determination has been amended as follows:

- VBA looks leftwards from the last column of the worksheet until it finds an occupied cell in row1 (=last column)
- VBA looks upwards from the last row of the worksheet until it finds an occupied cell in Column "A" (=last row)

So that means that your last column must have a header in row1 and your last row must have a value in column "A")
Will this always be the case?

I have also reduced the strain on memory by amending the copy/paste line to

Please Login or Register  to view this content.

This means that it is only copying the columns in the data range.
I had been lazy and (by using .cells as the original range) was asking VBA to copy all the way across to column "XFD", which made your computer splutter!

Test database expanded to include blank columns "H" and "M"
Formula =IFERROR(SEARCH("P1",E2),"") is included in column "AA" simply allow me to check the results quickly.

Please Login or Register  to view this content.

[jindon]

Here's how I do

Please Login or Register  to view this content.

[jsolder]

Thanks guys, both of these work! Is there any advantage to using one over the other?

[jindon]

Filter is faster and code is easier, shorter in this case.

[kev_]

You are welcome.
Thanks for the reps

[mag1c]

Here's how I do

Please Login or Register  to view this content.

Hi Jindon,

I am trying to do something similar to the OP.

I would like to search my first sheet "MP List" in columns C, E and F for two pieces of text "cost" and "cc" and if they exist in any cell in those 3 columns, copy the entire row to a new sheet called "Cost" and delete them from the original sheet.

Could you help me understand your code so I can accomplish that?