How to Delete Entire Row in Excel using VBA – 5 Detailed Examples

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 –

Download the Excel Workbook below to follow along and understand How to Delete Entire Row in Excel using VBA – download excel workbookDelete-Entire-Row-in-Excel-using-VBA.xlsm

 

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, "A").End(xlUp).Row
For i = lastRow To 1 Step -1
If InStr(1, ws.Cells(i, 1).Value, "1001", vbTextCompare) > 0 Then
ws.Rows(i).Delete
End If
Next i
End Sub

All the rows that contain 1001 in column A will be deleted.

 

Conclusion

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.