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 the help of VBA to delete rows based on specific criteria in Excel. In this article, we will cover the following 5 approaches that can be used to delete entire row in Excel using VBA –
Table of Contents
Download the Excel Workbook below to follow along and understand How to Delete Entire Row in Excel using VBA – DOWNLOAD EXCEL WORKBOOK
Table of Contents
Method 1 – Delete Entire Row
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.
Follow the steps below to learn how to delete the entire first row of the active sheet –
STEP 1: Open the Workbook and go to Developer > Visual Basic to open the VBA editor.
STEP 2: Click on Insert > Module.
STEP 3: Write the following code –
Sub RemoveFirstRow() Rows(1).EntireRow.Delete End Sub
STEP 4: Close the VBA Editor and run the macro by pressing Alt + F8, selecting RemoveFirstRow, and clicking Run.
The 1st row will be deleted.
As you can see, the number inside the brackets (1 in this case) specifies the row to be deleted in Excel. For instance, if you intend to delete the 5th row, you can modify the code as follows –
Sub RemoveFirstRow() Rows(5).EntireRow.Delete End Sub
Method 2 – Delete Selected Rows
If you want to delete selected rows in Excel using VBA, you can use the following code.
Sub DeleteSelectedRows() On Error Resume Next Selection.EntireRow.Delete On Error GoTo 0 End Sub
You need to select the rows that you want to delete and then simply run this macro to get the results.
The selected rows will be deleted.
Method 3 – Delete Alternate Rows
If you want to delete alternate rows 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 –
Sub DeleteAlternateRows() Dim i As Long For i = ActiveSheet.UsedRange.Rows.Count To 1 Step -2 ActiveSheet.Rows(i).Delete Next i End Sub
After running this macro, the alternate rows will be removed.
Explore additional methods on how to delete alternate cells in Excel by clicking here.
Method 4 – Delete Blank Rows
If you want to delete blank rows in Excel using VBA, use the code provided below –
Sub DeleteBlankRows() Dim ws As Worksheet Dim rng As Range Dim row As Range Set ws = ActiveSheet Set rng = ws.UsedRange For Each row In rng.Rows If Application.CountA(row) = 0 Then row.Delete End If Next row End Sub
This VBA macro will iterate through each row in a specified range and delete any rows where all cells in that row are empty.
Click here to discover more methods on how to delete blank cells in Excel.
Method 5 – Delete Rows with Specified Word
If you want to delete rows in Excel that contain a specific word (e.g., “1001”), 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 if the word is found –
Sub DeleteRowsWithSpecificWord() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, &amp;amp;quot;A&amp;amp;quot;).End(xlUp).Row For i = lastRow To 1 Step -1 If InStr(1, ws.Cells(i, 1).Value, &amp;amp;quot;1001&amp;amp;quot;, vbTextCompare) &amp;amp;amp;amp;amp;amp;gt; 0 Then ws.Rows(i).Delete End If Next i End Sub
All the rows that contain 1001 in column A will be deleted.
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, catering to various scenarios, from deleting specific rows to handling alternates, blanks, and those containing specified words.
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.
Click here to learn more about VBA in Excel.