{"id":32868,"date":"2023-11-13T21:41:06","date_gmt":"2023-11-13T20:41:06","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=32868"},"modified":"2024-03-22T16:18:30","modified_gmt":"2024-03-22T15:18:30","slug":"delete-entire-row","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/delete-entire-row\/","title":{"rendered":"How to Delete Entire Row in Excel using VBA – 5 Detailed Examples"},"content":{"rendered":"

\"How<\/a><\/p>\n

Microsoft Excel is a powerful tool for data management and analysis, and Visual Basic for Applications (VBA<\/a><\/strong>) allows users to automate tasks and enhance the functionality<\/strong> of Excel. One of the most common tasks is to delete entire row<\/strong> in Excel.<\/p>\n

Even though it can easily be done in the workbook itself, you can need the help of VBA<\/a> to delete rows based on specific criteria<\/strong> in Excel. In this article, we will cover the following 5 approaches that can be used to delete entire row in Excel using VBA <\/strong>\u2013<\/p>\n

Download the Excel Workbook below to follow along and understand How to Delete Entire Row in Excel using VBA \u2013\u00a0download excel workbook<\/strong><\/span>Delete-Entire-Row-in-Excel-using-VBA.xlsm<\/span><\/a><\/h4>\n

 <\/p>\n

Method 1 \u2013 Delete Entire Row<\/span><\/strong><\/h3>\n

Using VBA, you can manually edit the code and mention the row number that you want to delete within the VBA Code. Suppose you want to delete the 1st row of the worksheet.<\/p>\n

Follow the steps below to learn how to delete the entire first row of the active sheet \u2013<\/p>\n

STEP 1:<\/span><\/strong>\u00a0Open the Workbook and go to\u00a0Developer > Visual Basic<\/strong>\u00a0to open the VBA editor.<\/p>\n

\"How<\/a><\/p>\n

STEP 2: <\/span><\/strong>Click on Insert > Module<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

STEP 3:<\/span> <\/strong>Write the following code<\/strong> \u2013<\/p>\n

Sub RemoveFirstRow()\r\nRows(1).EntireRow.Delete\r\nEnd Sub<\/pre>\n
\"How<\/a><\/p>\n
STEP 4:<\/span> <\/strong>Close the VBA Editor and run the macro by pressing Alt + F8<\/strong>, selecting RemoveFirstRow<\/strong>, and clicking Run<\/strong>.<\/p>\n
\"How<\/a><\/p>\n
The 1st row will be deleted.<\/p>\n
\"How<\/a><\/p>\n
As you can see, the number inside the brackets (1 in this case) specifies the row to be deleted<\/strong> in Excel. For instance, if you intend to delete the 5th row, you can modify the code<\/strong> as follows –<\/p>\n
Sub RemoveFirstRow()\r\nRows(5).EntireRow.Delete\r\nEnd Sub<\/pre>\n
 <\/p>\n
Method 2 \u2013 Delete Selected Rows<\/span><\/strong><\/h3>\n
If you want to delete selected rows in Excel<\/strong> using VBA, you can use the following code.<\/p>\n
Sub DeleteSelectedRows()\r\nOn Error Resume Next\r\nSelection.EntireRow.Delete\r\nOn Error GoTo 0\r\nEnd Sub<\/pre>\n
\"How<\/a><\/p>\n
You need to select the rows that you want to delete and then simply run this macro<\/strong> to get the results.<\/p>\n
\"How<\/a><\/p>\n
The selected rows will be deleted.<\/p>\n
\"How<\/a><\/p>\n
 <\/p>\n
Method 3 \u2013 Delete Alternate Rows<\/span><\/strong><\/h3>\n
If you want to delete alternate rows<\/strong> in Excel using VBA, you can modify the code to achieve this. The following VBA code will delete every other row, starting from the first row<\/strong> –<\/p>\n
Sub DeleteAlternateRows()\r\nDim i As Long\r\nFor i = ActiveSheet.UsedRange.Rows.Count To 1 Step -2\r\nActiveSheet.Rows(i).Delete\r\nNext i\r\nEnd Sub\r\n<\/pre>\n
\"How<\/a><\/p>\n
After running this macro, the alternate rows will be removed.<\/p>\n
\"How<\/a><\/p>\n
Explore additional methods on how to delete alternate cells in Excel by clicking here<\/strong><\/a>.<\/p>\n
 <\/p>\n
Method 4 \u2013 Delete Blank Rows<\/span><\/strong><\/h3>\n
If you want to delete blank rows in Excel using VBA, use the code provided below –<\/p>\n
Sub DeleteBlankRows()\r\nDim ws As Worksheet\r\nDim rng As Range\r\nDim row As Range\r\nSet ws = ActiveSheet\r\nSet rng = ws.UsedRange\r\nFor Each row In rng.Rows\r\nIf Application.CountA(row) = 0 Then\r\nrow.Delete\r\nEnd If\r\nNext row\r\nEnd Sub<\/pre>\n
\"How<\/a><\/p>\n
This VBA macro will iterate through each row in a specified range and delete<\/a> any rows where all cells in that row are empty<\/strong>.<\/p>\n
\"How<\/a><\/p>\n
Click here<\/strong><\/a> to discover more methods on how to delete blank cells in Excel.<\/p>\n
 <\/p>\n
Method 5 \u2013 Delete Rows with Specified Word<\/span><\/strong><\/h3>\n
If you want to delete rows in Excel that contain a specific word (e.g., “1001”)<\/strong>, you can use the following VBA code. This code searches for the specified word in a particular column (e.g., column A) and deletes the entire row<\/strong> if the word is found –<\/p>\n
Sub DeleteRowsWithSpecificWord()\r\nDim ws As Worksheet\r\nDim lastRow As Long\r\nDim i As Long\r\nSet ws = ActiveSheet\r\nlastRow = ws.Cells(ws.Rows.Count, &amp;amp;amp;amp;amp;quot;A&amp;amp;amp;amp;amp;quot;).End(xlUp).Row\r\nFor i = lastRow To 1 Step -1\r\nIf InStr(1, ws.Cells(i, 1).Value, &amp;amp;amp;amp;amp;quot;1001&amp;amp;amp;amp;amp;quot;, vbTextCompare) &amp;amp;amp;amp;amp;amp;amp;amp;amp;gt; 0 Then\r\nws.Rows(i).Delete\r\nEnd If\r\nNext i\r\nEnd Sub<\/pre>\n
\"How<\/a><\/p>\n
All the rows that contain 1001 in column A will be deleted.<\/p>\n
\"How<\/a><\/p>\n
 <\/p>\n
Conclusion<\/span><\/strong><\/h3>\n
Using VBA code in Excel provides a powerful way to automate tasks and streamline data manipulation. This article has outlined five distinct methods for deleting entire rows in Excel using VBA<\/a>, catering to various scenarios, from deleting specific rows to handling alternates, blanks, and those containing specified words.<\/p>\n
Be cautious when using these codes, as it will delete entire row in Excel, and the action cannot be undone. It’s advisable to back up your data or test the code on a small dataset before applying it to important information.<\/p>\n
Click here<\/strong><\/a> to learn more about VBA in Excel.<\/p>\n","protected":false},"excerpt":{"rendered":"
Microsoft Excel is a powerful tool for data management and analysis, and Visual Basic for Applications (VBA) allows users to automate tasks and enhance the functionality of Excel. One of the most common tasks is to delete entire row in Excel. Even though it can easily be done in the workbook itself, you can need […]<\/p>\n","protected":false},"author":1,"featured_media":33122,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"How to Delete Entire Row in Excel using VBA - 5 Detailed Examples","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[647,639,56],"tags":[1443,1444,1445],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/32868"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=32868"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/32868\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/33122"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=32868"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=32868"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=32868"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}