Search string in column then past entire row in new sheet

Neekaulth

New Member
Joined
Mar 18, 2011
Messages
2
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
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Bonjour. Try this

Code:
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

Board Regular
Joined
Feb 24, 2011
Messages
120
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

New Member
Joined
Mar 18, 2011
Messages
2
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!!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,652
Messages
5,838,603
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top