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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Upvote 0
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.
 
Upvote 0
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!!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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
Back
Top