{"id":27623,"date":"2022-12-25T12:34:33","date_gmt":"2022-12-25T07:04:33","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\/"},"modified":"2022-12-25T12:34:33","modified_gmt":"2022-12-25T07:04:33","slug":"solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\/","title":{"rendered":"[Solved] If cell in column contains specific word then cut the row of the specific word [closed]"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-62861469\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"62861469\" data-parentid=\"62861108\" data-score=\"-1\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>Try the next code, please (adapted to search in C:C the string occurrence):<\/p>\n<pre><code>Sub TestCutSUBRowsPaste()\n Dim sh As Worksheet, shDest As Worksheet, strSearch As String\n Dim i As Long, rngCut As Range, lastRowD As Long, lastRow As Long\n \n strSearch = \"POS\"\n Set sh = ActiveSheet\n Set shDest = Worksheets.aDD\n lastRow = sh.Range(\"A\" &amp; Rows.count).End(xlUp).row\n For i = 1 To lastRow\n    If InStr(sh.Range(\"C\" &amp; i).Value, strSearch) &gt; 0 Then\n        lastRowD = shDest.Range(\"A\" &amp; Rows.count).End(xlUp).row\n        sh.Rows(i).Cut shDest.Range(\"A\" &amp; lastRowD + 1)\n    End If\n Next i\nEnd Sub\n<\/code><\/pre>\n<p>How many such occurrences do you estimate to exist in your sheet to be processed? If a lot of them, I can adapt the code to use arrays and work fast enough for big ranges to be moved&#8230;<\/p>\n<p>Edited:<\/p>\n<p>The faster code variant, working in memory and dropping the processing result at once:<\/p>\n<pre><code>Sub TestCutSUBRowsPasteArrays()\n Dim sh As Worksheet, shDest As Worksheet, strSearch1 As String, strSearch2 As String\n Dim arr As Variant, arrCut As Variant, rngCut As Range, lastRow As Long, lastCol As Long\n Dim k As Long, i As Long, j As Long\n \n strSearch1 = \"POS\": strSearch2 = \"Iyzico\"\n Set sh = ActiveSheet\n Set shDest = Worksheets.Add\n lastRow = sh.Range(\"A\" &amp; Rows.Count).End(xlUp).Row\n 'determine of the last (existing) column:\n lastCol = sh.Cells(1, Columns.Count).End(xlToLeft).Column\n 'load all the range to be processed in an array:\n arr = sh.Range(\"A2\", sh.Cells(lastRow, lastCol)).Value\n 'initially redim the array at the total range dimesions\n ReDim arrCut(1 To lastCol, 1 To UBound(arr, 1))\n \n For i = 1 To UBound(arr)\n    If InStr(arr(i, 3), strSearch1) &gt; 0 Or _\n                      InStr(arr(i, 3), strSearch2) &gt; 0 Then\n        'if one of the search string exists:\n        k = k + 1 'increment the array row\n        For j = 1 To lastCol\n            arrCut(j, k) = arr(i, j) 'load the final array with cut elements\n            arr(i, j) = \"\" 'eliminate the elements from initial array\n        Next\n    End If\n Next i\n 'if no occurrences found, the code will exit:\n If k = 0 Then MsgBox \"No occurrence foung in column C:C...\": Exit Sub\n 'Redim the array to the exact limit containing values:\n ReDim Preserve arrCut(1 To lastCol, 1 To k)\n 'dropping the initial array (remained) values:\n sh.Range(\"A2\", sh.Cells(lastRow, lastCol)).Value = arr\n 'Dropping the processed array (arrCut) at once:\n shDest.Range(\"A2\").Resize(UBound(arrCut, 2), _\n        UBound(arrCut, 1)).Value = WorksheetFunction.Transpose(arrCut)\nEnd Sub\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">22<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved If cell in column contains specific word then cut the row of the specific word [closed] <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Try the next code, please (adapted to search in C:C the string occurrence): Sub TestCutSUBRowsPaste() Dim sh As Worksheet, shDest As Worksheet, strSearch As String Dim i As Long, rngCut As Range, lastRowD As Long, lastRow As Long strSearch = &#8220;POS&#8221; Set sh = ActiveSheet Set shDest = Worksheets.aDD lastRow = sh.Range(&#8220;A&#8221; &amp; Rows.count).End(xlUp).row &#8230; <a title=\"[Solved] If cell in column contains specific word then cut the row of the specific word [closed]\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\/\" aria-label=\"More on [Solved] If cell in column contains specific word then cut the row of the specific word [closed]\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[400,401],"class_list":["post-27623","post","type-post","status-publish","format-standard","hentry","category-solved","tag-excel","tag-vba"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>[Solved] If cell in column contains specific word then cut the row of the specific word [closed] - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] If cell in column contains specific word then cut the row of the specific word [closed] - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Try the next code, please (adapted to search in C:C the string occurrence): Sub TestCutSUBRowsPaste() Dim sh As Worksheet, shDest As Worksheet, strSearch As String Dim i As Long, rngCut As Range, lastRowD As Long, lastRow As Long strSearch = &quot;POS&quot; Set sh = ActiveSheet Set shDest = Worksheets.aDD lastRow = sh.Range(&quot;A&quot; &amp; Rows.count).End(xlUp).row ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-12-25T07:04:33+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\\\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] If cell in column contains specific word then cut the row of the specific word [closed]\",\"datePublished\":\"2022-12-25T07:04:33+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\\\/\"},\"wordCount\":103,\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"keywords\":[\"excel\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\\\/\",\"name\":\"[Solved] If cell in column contains specific word then cut the row of the specific word [closed] - JassWeb\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\"},\"datePublished\":\"2022-12-25T07:04:33+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] If cell in column contains specific word then cut the row of the specific word [closed]\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777613206\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777613206\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777613206\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\\\/\\\/jassweb.com\"],\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/author\\\/jaspritsinghghumangmail-com\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] If cell in column contains specific word then cut the row of the specific word [closed] - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] If cell in column contains specific word then cut the row of the specific word [closed] - JassWeb","og_description":"[ad_1] Try the next code, please (adapted to search in C:C the string occurrence): Sub TestCutSUBRowsPaste() Dim sh As Worksheet, shDest As Worksheet, strSearch As String Dim i As Long, rngCut As Range, lastRowD As Long, lastRow As Long strSearch = \"POS\" Set sh = ActiveSheet Set shDest = Worksheets.aDD lastRow = sh.Range(\"A\" &amp; Rows.count).End(xlUp).row ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\/","og_site_name":"JassWeb","article_published_time":"2022-12-25T07:04:33+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] If cell in column contains specific word then cut the row of the specific word [closed]","datePublished":"2022-12-25T07:04:33+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\/"},"wordCount":103,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["excel","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\/","url":"https:\/\/jassweb.com\/solved\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\/","name":"[Solved] If cell in column contains specific word then cut the row of the specific word [closed] - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-12-25T07:04:33+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-if-cell-in-column-contains-specific-word-then-cut-the-row-of-the-specific-word-closed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] If cell in column contains specific word then cut the row of the specific word [closed]"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777613206","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777613206","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777613206","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/27623","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=27623"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/27623\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=27623"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=27623"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=27623"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}