Page 3 of 3 FirstFirst 123
Results 21 to 26 of 26

Thread: Cut row to new sheet if text is found within multiple columns
Thanks Thanks: 0 Likes Likes: 0

  1. #21
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,226
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

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

    Quote Originally Posted by Mike___ View Post
    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.
    Okay, give this macro a try...
    Code:
    Sub Copy_Rows_v2() Dim LastRow1 As Long, LastRow2 As Long, BlankCol As Long, strToFind As String, WS1 As Worksheet, WS2 As Worksheet Set WS1 = Sheets("Sheet1") Set WS2 = Sheets("Sheet2") BlankCol = WS1.Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column + 1 LastRow1 = WS1.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row On Error Resume Next LastRow2 = WS2.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row If Err.Number Then LastRow2 = 1 On Error GoTo 0 strToFind = InputBox("Enter Keyword to be found") With WS1.Range(Cells(2, BlankCol), Cells(LastRow1, BlankCol)) .Formula = "=MATCH(""" & strToFind & "*"",'" & WS1.Name & "'!AE2:AM2,0)" On Error Resume Next Intersect(WS1.Columns("A"), .SpecialCells(xlFormulas, xlNumbers).EntireRow).EntireRow.Copy WS2.Cells(LastRow2 + 1, "A") On Error GoTo 0 End With WS1.Columns(BlankCol).Clear WS2.Columns(BlankCol).Clear End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  2. #22
    New Member
    Join Date
    Jul 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi Peter,

    It was my turn for a mistake - I had been using the old code. It works a treat! Thanks for your time on this.

    Rick - I haven't had a chance to test your new code yet - I shall give it a go!

  3. #23
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,226
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

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

    Quote Originally Posted by Mike___ View Post
    Rick - I haven't had a chance to test your new code yet - I shall give it a go!
    I would be interested to know if the code I posted worked or not. Also, if it works, your sense how it compared speedwise to Peter's code.
    Last edited by Rick Rothstein; Jul 26th, 2019 at 06:23 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #24
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,972
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

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

    Quote Originally Posted by Mike___ View Post
    Hi Peter,

    It works a treat! Thanks for your time on this.
    Great! You are very welcome.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #25
    New Member
    Join Date
    Jul 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi Rick,

    Both codes work perfectly and both ran 10k of rows instantaneously.

    Thanks once again to you both!

  6. #26
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,226
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

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

    Quote Originally Posted by Mike___ View Post
    Hi Rick,

    Both codes work perfectly and both ran 10k of rows instantaneously.

    Thanks once again to you both!
    Thanks for getting back to me on that... much appreciated. And, of course, you are quite welcome.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •