{"id":4231,"date":"2019-04-04T07:30:37","date_gmt":"2019-04-04T07:30:37","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=4231"},"modified":"2024-03-29T15:48:44","modified_gmt":"2024-03-29T15:48:44","slug":"delete-a-row-if-cell-contains","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/","title":{"rendered":"VBA Delete a Row if the Cell Contains"},"content":{"rendered":"\n<div class=\"wp-block-file\"><a id=\"wp-block-file--media-0db2f2ca-e4e5-4ee1-816d-979c05613260\" href=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/12\/VBA-Delete-a-Row-if-the-Cell-Contains.xlsx\">VBA-Delete-a-Row-if-the-Cell-Contains<\/a><a href=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/12\/VBA-Delete-a-Row-if-the-Cell-Contains.xlsx\" class=\"wp-block-file__button wp-element-button\" download aria-describedby=\"wp-block-file--media-0db2f2ca-e4e5-4ee1-816d-979c05613260\">Download File<\/a><\/div>\n\n\n\n<p>I\u2019ve already written about removing rows in Excel, that contain specific text. In this lesson, I\u2019ll show you have to achieve this with a single click, instead of using all these steps.<\/p>\n\n\n\n<p>In order to open the VBA window, press <strong>Alt + F11<\/strong>.<\/p>\n\n\n\n<p>Now we have three options to remove a row. <\/p>\n\n\n\n<ol>\n<li>If\nthe cell is the same as the string.<\/li>\n\n\n\n<li>Case\ninsensitive.<\/li>\n\n\n\n<li>If\nthe cell\n<a href=\"https:\/\/officetuts.net\/excel\/vba\/check-if-a-cell-contains-a-letter-or-string\/\">contains the string<\/a>.<\/li>\n<\/ol>\n\n\n\n<p>Our string will be called \u201cdelete\u201d. Let\u2019s create three\nmacros to deal with these three problems.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">If the cell is the same as the string<\/h2>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"255\" height=\"100\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/exactly-the-cell-value.png\" alt=\"\" class=\"wp-image-4233\"\/><\/figure>\n\n\n\n<p>The following example has the words \u201cdelete\u201d highlighted. But not all of them will be deleted. We are going to delete only those rows that consist of cells whose value equals \u201cdelete\u201d.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"270\" height=\"240\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/highlighted-cells.png\" alt=\"\" class=\"wp-image-4234\"\/><\/figure>\n\n\n\n<p><a href=\"https:\/\/officetuts.net\/excel\/training\/selecting-cells\/\">Select cells<\/a> from <strong>A1<\/strong> to <strong>B10<\/strong> and run the following code.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Sub DeleteRows()\n    Dim rng As Range\n    Set rng = ActiveSheet.UsedRange\n    \n    For i = rng.Cells.Count To 1 Step -1\n        If rng.Item(i).Value = \"delete\" Then\n            rng.Item(i).EntireRow.Delete\n        End If\n    Next i\nEnd Sub<\/code><\/pre>\n\n\n\n<p>This is going to generate the following result.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"270\" height=\"200\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/delete-rows-1.png\" alt=\"\" class=\"wp-image-4235\"\/><\/figure>\n\n\n\n<p>Rows with cells whose value equals \u201cDelete\u201d won\u2019t be affected because our example is case-sensitive.<\/p>\n\n\n\n<p><strong>Code explanation:<\/strong><\/p>\n\n\n\n<p><strong>2 and 3<\/strong>. We\ndeclare the range variable and then assign selected cells to this range.<\/p>\n\n\n\n<p><strong>5<\/strong>. Loop through from the last to the first cell.<\/p>\n\n\n\n<p><strong>6 and 7<\/strong>. If the <a href=\"https:\/\/officetuts.net\/excel\/formulas\/get-cell-value\/\">value of the selected cell<\/a> is exactly \u201cdelete\u201d\nthen remove the entire row.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Case insensitive<\/h2>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"255\" height=\"100\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/if-cell-is-the-same-as-the-string.png\" alt=\"\" class=\"wp-image-4236\"\/><\/figure>\n\n\n\n<p>This example is similar to the previous one, but this time we are going to treat the uppercase\nand lowercase letters the same way.<\/p>\n\n\n\n<p>It\u2019s very easy to change. At the beginning of your code insert the following line:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Option Compare Text<\/code><\/pre>\n\n\n\n<p>Now, if you run the code the result will be quite different.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"270\" height=\"160\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/ignore-case.png\" alt=\"\" class=\"wp-image-4237\"\/><\/figure>\n\n\n\n<p>If you don\u2019t want to use <strong>Option Compare Text<\/strong>, you can change this line:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>If rng.Item(i).Value = \"delete\" Then<\/code><\/pre>\n\n\n\n<p>To this one:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>If LCase(rng.Item(i).Value) = LCase(\"delete\") Then<\/code><\/pre>\n\n\n\n<p>It will change both strings to lowercase while comparing\nvalues.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">If the cell contains the string<\/h2>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"255\" height=\"80\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/case-sensitive.png\" alt=\"\" class=\"wp-image-4238\"\/><\/figure>\n\n\n\n<p>This is the last example. This procedure will remove every row that has the word \u201cdelete\u201d inside. This example is case insensitive.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Sub DeleteRows()\n    Dim rng As Range\n    Dim pos As Integer\n    Set rng = ActiveSheet.UsedRange\n    \n    For i = rng.Cells.Count To 1 Step -1\n        pos = InStr(LCase(rng.Item(i).Value), LCase(\"delete\"))\n        If pos &gt; 0 Then\n            rng.Item(i).EntireRow.Delete\n        End If\n    Next i\nEnd Sub<\/code><\/pre>\n\n\n\n<p><strong>Code explanation:<\/strong><\/p>\n\n\n\n<p>The <strong>InStr<\/strong>\nfunction takes two arguments and returns the position of the first occurrence\nof the word from the second parameter. If the word is not present then it\nreturns 0.<\/p>\n\n\n\n<p>If the position is greater than 0 then remove the row.<\/p>\n\n\n\n<p>If you run this code, this is the result you are going to\nget this result.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"206\" height=\"120\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/cell-consists-insensitive.png\" alt=\"\" class=\"wp-image-4239\"\/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>I\u2019ve already written about removing rows in Excel, that contain specific text. In this lesson, I\u2019ll show you have to achieve this with&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19],"tags":[170,190],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>VBA Delete a Row if the Cell Contains<\/title>\n<meta name=\"description\" content=\"Learn how to delete rows in Excel that contain specific text with just one click using VBA. Download the example file and follow the step-by-step guide.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"VBA Delete a Row if the Cell Contains\" \/>\n<meta property=\"og:description\" content=\"Learn how to delete rows in Excel that contain specific text with just one click using VBA. Download the example file and follow the step-by-step guide.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-04T07:30:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-29T15:48:44+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/exactly-the-cell-value.png\" \/>\n<meta name=\"author\" content=\"Tomasz Decker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tomasz Decker\" \/>\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:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"VBA Delete a Row if the Cell Contains\",\"datePublished\":\"2019-04-04T07:30:37+00:00\",\"dateModified\":\"2024-03-29T15:48:44+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/\"},\"wordCount\":383,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/exactly-the-cell-value.png\",\"keywords\":[\"file\",\"pinterest\"],\"articleSection\":[\"vba\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/\",\"url\":\"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/\",\"name\":\"VBA Delete a Row if the Cell Contains\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/exactly-the-cell-value.png\",\"datePublished\":\"2019-04-04T07:30:37+00:00\",\"dateModified\":\"2024-03-29T15:48:44+00:00\",\"description\":\"Learn how to delete rows in Excel that contain specific text with just one click using VBA. Download the example file and follow the step-by-step guide.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/exactly-the-cell-value.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/exactly-the-cell-value.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"VBA Delete a Row if the Cell Contains\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"VBA Delete a Row if the Cell Contains","description":"Learn how to delete rows in Excel that contain specific text with just one click using VBA. Download the example file and follow the step-by-step guide.","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:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/","og_locale":"en_US","og_type":"article","og_title":"VBA Delete a Row if the Cell Contains","og_description":"Learn how to delete rows in Excel that contain specific text with just one click using VBA. Download the example file and follow the step-by-step guide.","og_url":"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/","article_published_time":"2019-04-04T07:30:37+00:00","article_modified_time":"2024-03-29T15:48:44+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/exactly-the-cell-value.png"}],"author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"VBA Delete a Row if the Cell Contains","datePublished":"2019-04-04T07:30:37+00:00","dateModified":"2024-03-29T15:48:44+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/"},"wordCount":383,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/exactly-the-cell-value.png","keywords":["file","pinterest"],"articleSection":["vba"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/","url":"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/","name":"VBA Delete a Row if the Cell Contains","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/exactly-the-cell-value.png","datePublished":"2019-04-04T07:30:37+00:00","dateModified":"2024-03-29T15:48:44+00:00","description":"Learn how to delete rows in Excel that contain specific text with just one click using VBA. Download the example file and follow the step-by-step guide.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/exactly-the-cell-value.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/04\/exactly-the-cell-value.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/vba\/delete-a-row-if-cell-contains\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"VBA Delete a Row if the Cell Contains"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/4231"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=4231"}],"version-history":[{"count":10,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/4231\/revisions"}],"predecessor-version":[{"id":13022,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/4231\/revisions\/13022"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=4231"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=4231"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=4231"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}