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 WORKBOOK

 

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.

See also  Save Selected Range as PDF Using Macros In Excel

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.

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

STEP 2: Click on Insert > Module.

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

STEP 3: Write the following code

Sub RemoveFirstRow()
Rows(1).EntireRow.Delete
End Sub

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

STEP 4: Close the VBA Editor and run the macro by pressing Alt + F8, selecting RemoveFirstRow, and clicking Run.

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

The 1st row will be deleted.

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

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

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

You need to select the rows that you want to delete and then simply run this macro to get the results.

See also  Understanding the VBA MsgBox Function: A Comprehensive Guide

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

The selected rows will be deleted.

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

 

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

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

After running this macro, the alternate rows will be removed.

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

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

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

This VBA macro will iterate through each row in a specified range and delete any rows where all cells in that row are empty.

See also  Disable/Enable Get Pivot Data Using Macros In Excel

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

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

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

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

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

 

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.

See also  Highlight Max Value in Selection Using Macros In Excel

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.

HELPFUL RESOURCES:

JOIN OUR FREE EXCEL COURSES BELOW
Click here to access these FREE Excel courses!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin