Search string in column then past entire row in new sheet

[Neekaulth]

Hello MrExcel forum.

I have been searching for a few hours now on this subject, found a handful of pieces of code but couldn't get a single one to work after my modification. So here I come begging for professionnal help

First of all, sorry for my english I am a native french speaker. Secondly, although I love programming and such I am a mechincal engineer - so my knowledge in programming is somewhat limited.

Here is what I am try to do:

I have a worksheet that contains 3 columns. I want to search trough all of column B for a certain string "BEARING OPTION (IC)". When this string is found, I would like this ENTIRE row to be cut and pasted into the first empty row of Sheet2.

Here is an example of my worksheet.


<TABLE style="WIDTH: 521pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=693 border=0 x:str><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 256pt; mso-width-source: userset; mso-width-alt: 12470" width=341><COL style="WIDTH: 182pt; mso-width-source: userset; mso-width-alt: 8850" width=242><TBODY><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=110 height=18 x:num>3028007</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 256pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=341>BEARING-RLR,FLG,3.1498X4.3699X.722</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 182pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=242>RELEASED TO PRODUCTION</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=110 height=18 x:num>3029083</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 256pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=341>BEARING-BALL,ANNULAR,NO.2</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 182pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=242>RELEASED TO PRODUCTION</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=110 height=18 x:num>3029274</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 256pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=341>BEARING-PLAIN,2.125X2.345X1.072LG</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 182pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=242>RELEASED TO PRODUCTION</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=110 height=18 x:num>3029309</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 256pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=341>BEARING-SPHERICAL,190X.300X.625</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 182pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=242>RELEASED TO PRODUCTION</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=110 height=18 x:num>3029317</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 256pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=341>BEARING-SPHERICAL,.190X.300X.625</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 182pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=242>RELEASED TO PRODUCTION</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=110 height=18 x:num>3029318</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 256pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=341>BEARING OPTION (IC)</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 182pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=242>RELEASED TO PRODUCTION</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=110 height=18 x:num>3029729</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 256pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=341>BEARING OPTION (IC)</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 182pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=242>RELEASED TO PRODUCTION</TD></TR></TBODY></TABLE>

In this example, I would like to cut and paste the last 2 entries into Sheet2, so I would end up with a Sheet 1 WITHOUT any entries containing "BEARING OPTION (IC)" in column B, ans a Sheet2 containing all of these entries.

I do not need to "delete" the row that had the entry, if it is an empty row I can easily run a macro to delete all empty rows.


I hope I made myself clear enough

Thanks for your time!


Jean-Nicolas

 

[VoG]

Bonjour. Try this

Sub btest()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("B" & i).Value = "BEARING OPTION (IC)" Then Rows(i).Cut Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[bryonwoods30]

If you want something quick, you could just filter the data and filter by "BEARING OPTION (IC)" in column b, then just quickly highlight,copy and paste those rows to your new sheet. that only takes a few seconds to do but I understand if you want a macro that will do it for you.

 

[Neekaulth]

Wow, Merci beacoup! for the fast reply. Worked like a charm!

Now I can finish this goddamnd atabase and return to modeling parts

Thanks a lot again!!