Cut row to new sheet if text is found within multiple columns

[Mike___]

Hi all,

I currently use conditional formatting for this task - but I am finding myself needing to do this more often so wished to set up a VBA rather than use a long winded work around. In the past I have successfully created VBA's by mixing and matching various codes. Sadly - I am unable to find a solution to this and I know it is a pretty easy one which I am finding annoying.

I am looking to search columns C to K for text (normally it is just a partial match I am after). And if found the entire row is cut and moved to sheet2. There will be some blank cells in the columns and the documents could contain 20 - 40k rows of data.

The below code works on a search for just column W but I was unable to add multiple columns to the code - Sorry. I believe the answers are contained in this link [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.mrexcel.com/forum/excel-questions/855173-vba-lastrow.html but my attempts of adding to this code myself have failed.[/FONT]

The search term that I will be using will constantly change (in this instance Business*) - Although I could just change it each time in the VBA - in a perfect world I would like to add the word or partial word to be found in a box when the VBA is run to speed up the process.

If anyone could help I would be most grateful.

Thanks
Mike

Option Explicit
Sub Test()

Dim sht1 As Worksheet, sht2 As Worksheet
Dim i As Long

Set sht1 = ThisWorkbook.Worksheets("Sheet1")
Set sht2 = ThisWorkbook.Worksheets("Sheet2")

For i = 2 To sht1.Cells(sht1.Rows.Count, "w").End(xlUp).Row
If sht1.Range("w" & i).Value Like "Business*" Then
sht1.Range("A" & i).EntireRow.Cut sht2.Range("A" & sht2.Cells(sht2.Rows.Count, "w").End(xlUp).Row + 1)
End If
Next i

End Sub

 

[Mike___]

I found out the problem.

The data in columns AE:AM contains various keywords but after the majority of them it contains a semi colon and a number eg Science;13 or Team Building;9.

When I tested your code with the semi colon and number removed it worked. As soon as I added it back it didn't.

I looked to attach a sample doc for you to test but I do not have the appropriate permissions. Apologies.

 

[Peter_SSs]

I looked to attach a sample doc for you to test but I do not have the appropriate permissions. Apologies.

It isn't a matter of permissions as the forum does not allow attachments. However, you can post small screen shots of your worksheet(s). Follow the 'Look here' link in my signature block below to find out how.

 

[Mike___]

[TABLE="class:grid"]
<colgroup><col width="72" span="9" style="width:54pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 72"]Keyword[/TD]
[TD="class: xl65, width: 72"]Keyword 2[/TD]
[TD="class: xl65, width: 72"]Keyword 3[/TD]
[TD="class: xl65, width: 72"]Keyword 4[/TD]
[TD="class: xl65, width: 72"]Keyword 5[/TD]
[TD="class: xl65, width: 72"]Keyword 6[/TD]
[TD="class: xl65, width: 72"]Keyword 7[/TD]
[TD="class: xl65, width: 72"]Keyword 8[/TD]
[TD="class: xl65, width: 72"]Keyword 9[/TD]
[/TR]
[TR]
[TD="class: xl66"]Biomedical research;1[/TD]
[TD="class: xl66"]Medical genetics;1[/TD]
[TD="class: xl66"]R scripting;1[/TD]
[TD="class: xl66"]Unix scripting;1[/TD]
[TD="class: xl66"]NGS-tools;2[/TD]
[TD="class: xl66"]Next-generation sequencing;2[/TD]
[TD="class: xl66"]Bioinformatics;3[/TD]
[TD="class: xl66"]Genomics;3[/TD]
[TD="class: xl66"]Molecular Diagnostics;9[/TD]
[/TR]
[TR]
[TD="class: xl66"]Cell Culture;1[/TD]
[TD="class: xl66"]Chromatography;1[/TD]
[TD="class: xl66"]HPLC;1[/TD]
[TD="class: xl66"]Lifesciences;1[/TD]
[TD="class: xl66"]Biotechnology;2[/TD]
[TD="class: xl66"]GLP;3[/TD]
[TD="class: xl66"]PCR;3[/TD]
[TD="class: xl66"]Molecular Biology;7[/TD]
[TD="class: xl66"]Microbiology;9[/TD]
[/TR]
[TR]
[TD="class: xl66"]DNA electrophoresis;3[/TD]
[TD="class: xl66"]DNA sequencing;3[/TD]
[TD="class: xl66"]PCR primer design;3[/TD]
[TD="class: xl66"]DNA extraction;4[/TD]
[TD="class: xl66"]Genotyping;4[/TD]
[TD="class: xl66"]PCR;4[/TD]
[TD="class: xl66"]Sequencing;4[/TD]
[TD="class: xl66"]Western Blotting;5[/TD]
[TD="class: xl66"]Genomics;6[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Diagnostics;2[/TD]
[TD="class: xl66"]Life Sciences;2[/TD]
[TD="class: xl66"]qPCR;2[/TD]
[TD="class: xl66"]Biotechnology;3[/TD]
[TD="class: xl66"]Cell Culture;3[/TD]
[TD="class: xl66"]Research;3[/TD]
[TD="class: xl66"]Genetics;4[/TD]
[TD="class: xl66"]Biochemistry;5[/TD]
[TD="class: xl66"]Molecular Biology;5[/TD]
[/TR]
[TR]
[TD="class: xl66"]Biotechnology;1[/TD]
[TD="class: xl66"]Cell Culture;1[/TD]
[TD="class: xl66"]Clinical Research;1[/TD]
[TD="class: xl66"]Immunoassays;1[/TD]
[TD="class: xl66"]Infectious Diseases;1[/TD]
[TD="class: xl66"]Lifesciences;1[/TD]
[TD="class: xl66"]Microbiology;1[/TD]
[TD="class: xl66"]Molecular Biology;1[/TD]
[TD="class: xl66"]PCR;1[/TD]
[/TR]
[TR]
[TD="class: xl66"]Infectious Diseases;4[/TD]
[TD="class: xl66"]Medical Devices;5[/TD]
[TD="class: xl66"]Sequencing;7[/TD]
[TD="class: xl66"]Pharmaceutical Industry;8[/TD]
[TD="class: xl66"]qPCR;9[/TD]
[TD="class: xl66"]Genomics;14[/TD]
[TD="class: xl66"]Lifesciences;14[/TD]
[TD="class: xl66"]Biotechnology;35[/TD]
[TD="class: xl66"]Molecular Biology;45[/TD]
[/TR]
[TR]
[TD="class: xl66"]Biochemistry;1[/TD]
[TD="class: xl66"]Durum Wheat;1[/TD]
[TD="class: xl66"]MicroRNA Profiling;1[/TD]
[TD="class: xl66"]Molecular Biology;1[/TD]
[TD="class: xl66"]Next-generation Sequencing;1[/TD]
[TD="class: xl66"]Plant Physiology;1[/TD]
[TD="class: xl66, colspan: 2"]Scientific Writing;1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]DNA sequencing;1[/TD]
[TD="class: xl66"]Epigenetics;1[/TD]
[TD="class: xl66"]Lifesciences;1[/TD]
[TD="class: xl66"]Prostate Cancer;1[/TD]
[TD="class: xl66"]qPCR;2[/TD]
[TD="class: xl66"]Genomics;5[/TD]
[TD="class: xl66"]Research;7[/TD]
[TD="class: xl66"]Genetics;16[/TD]
[TD="class: xl66"]Molecular Biology;19[/TD]
[/TR]
[TR]
[TD="class: xl66"]CMV;1[/TD]
[TD="class: xl66"]HPV;1[/TD]
[TD="class: xl66"]Herpes;1[/TD]
[TD="class: xl66"]Instagram;1[/TD]
[TD="class: xl66"]NCBI;1[/TD]
[TD="class: xl66"]iPhone;1[/TD]
[TD="class: xl66"]iPhoto;1[/TD]
[TD="class: xl66"]Multiplex PCR;3[/TD]
[TD="class: xl66"]PCR;9[/TD]
[/TR]
[TR]
[TD="class: xl66"]Genomics;1[/TD]
[TD="class: xl66"]Lifesciences;1[/TD]
[TD="class: xl66"]Lecturing;3[/TD]
[TD="class: xl66"]Proteomics;4[/TD]
[TD="class: xl66"]RT-PCR;5[/TD]
[TD="class: xl66"]Clinical Research;7[/TD]
[TD="class: xl66"]PCR;8[/TD]
[TD="class: xl66"]Molecular Biology;15[/TD]
[TD="class: xl66"]Cell Culture;21[/TD]
[/TR]
[TR]
[TD="class: xl66"]Blood Bank;1[/TD]
[TD="class: xl66"]Hormones;1[/TD]
[TD="class: xl66"]Nanoparticles;1[/TD]
[TD="class: xl66"]PLGA;1[/TD]
[TD="class: xl66"]Serology;1[/TD]
[TD="class: xl66"]Laboratory Skills;3[/TD]
[TD="class: xl66"]Biochemistry;4[/TD]
[TD="class: xl66"]Microbiology;4[/TD]
[TD="class: xl66"]Laboratory Medicine;12[/TD]
[/TR]
[TR]
[TD="class: xl66"]Agriculture;1[/TD]
[TD="class: xl66"]Biotechnology;1[/TD]
[TD="class: xl66"]GMP;1[/TD]
[TD="class: xl66"]Lifesciences;1[/TD]
[TD="class: xl66"]Molecular Biology;1[/TD]
[TD="class: xl66"]PCR;1[/TD]
[TD="class: xl66"]R&D;1[/TD]
[TD="class: xl66"]Six Sigma;1[/TD]
[TD="class: xl66"]Validation;1[/TD]
[/TR]
[TR]
[TD="class: xl66"]qPCR;13[/TD]
[TD="class: xl66"]Flow Cytometry;16[/TD]
[TD="class: xl66"]PCR;27[/TD]
[TD="class: xl66"]Cell Biology;46[/TD]
[TD="class: xl66"]Biochemistry;48[/TD]
[TD="class: xl66"]Molecular Cloning;56[/TD]
[TD="class: xl66"]Cell Culture;59[/TD]
[TD="class: xl66"]Western Blotting;74[/TD]
[TD="class: xl66"]Molecular Biology;93[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Thanks for the sample data.
Oops, big mistake on my part, sorry about that. I had two arguments in the Instr function the wrong way around. It should be

If InStr(1, a(i, j), strToFind, 1) > 0 Then

 

[Mike___]

Thanks for your help with this - it now copies across perfectly.

There is one last remaining issue that doesn't quite work as requested. Each time I run a new search it wipes over what has previously been found rather than adding to the sheet 2 results.

Apologies for taking up your time on this - but it is much appreciated.

 

[Peter_SSs]

I had assumed that we could tell the last row used in Sheet2 by looking for the bottom of column A. Apparently that is not the case. Try changing this line.

With ws2
  lr = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByRows, _
          SearchDirection:=xlPrevious, SearchFormat:=False).Row
End With

 

[Mike___]

Hmmm I am afraid we are back to nothing copying across again?

 

[Rick Rothstein]

Assuming you specify the unique beginning text, at minimum, for the keyword you want to search for (for example, "Cell C" for "Cell Culture" would be sufficient although you can type more letters of the keyword if you wish), then does this macro work for you...

[table="width: 500"]
[tr]
	[td]Sub Copy_Rows_v2()
  Dim LastRow1 As Long, LastRow2 As Long, strToFind As String
  LastRow1 = Sheets("[B][COLOR="#0000FF"]Sheet1[/COLOR][/B]").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  LastRow2 = Sheets("[B][COLOR="#FF0000"]Sheet2[/COLOR][/B]").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  strToFind = InputBox("Enter Keyword to be found")
  With Sheets("[B][COLOR="#0000FF"]Sheet1[/COLOR][/B]").Range("AN2:AN" & LastRow1)
    .Formula = "=MATCH(""" & strToFind & "*"",[B][COLOR="#0000FF"]Sheet1[/COLOR][/B]!AE2:AM2,0)"
    On Error Resume Next
    Intersect(Sheets("[B][COLOR="#0000FF"]Sheet1[/COLOR][/B]").Columns("AE:AM"), .SpecialCells(xlFormulas, xlNumbers).EntireRow).Copy Sheets("[B][COLOR="#FF0000"]Sheet2[/COLOR][/B]").Cells(LastRow2 + 1, "A")
    On Error GoTo 0
  End With
  Sheets("[B][COLOR="#0000FF"]Sheet1[/COLOR][/B]").Columns("AN").Clear
End Sub[/td]
[/tr]
[/table]

 

[Mike___]

Hi Rick,

This copies across the results to columns A to I in sheet 2 rather than to AE to AM.
I need the entire row copied across if keyword is found as there is data in the other columns that needs to move across too.

 

[Peter_SSs]

Hmmm I am afraid we are back to nothing copying across again?

The only effect of the change in post 16 is to determine what row to paste the data in Sheet2, not what is copied.

Are you sure that you still have the changed line from post 14 & didn't revert to the old version of that code line?