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:

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,368
Messages
5,528,276
Members
409,814
Latest member
Leon_Al

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top