Excel VBA Delete Row: Get Rid of Stuff You Don’t Need

At times, when you’re working with multiple spreadsheets, you may want to delete some rows because they are blank, duplicate or otherwise no longer required. Excel VBA is a powerful programming language that enables us to delete rows efficiently with ease. Or that matter, automate any repetitive task with ease. We will walk you through how to automate deleting rows in Excel using VBA. We assume you are familiar with the basics of MS Excel VBA.

If you’re new to Excel VBA we suggest that you first take this basic course on Excel VBA, or perhaps, brush up basic concepts with this VBA tutorial.

For all the examples we use in this tutorial, here are some generic instructions:

1. First open an existing Excel workbook which has data.
2. Press Alt+F11 simultaneously which will open the VBA editor.
3. Then insert a module using Insert menu.
4. To try these examples, copy and paste the code given in this tutorial into the code window. Alternatively, you can manually type in the code.
5. Finally, save the file as macro.

To execute the code press the F5 function key. Now lets move on to the practical aspect of deleting rows in Excel.

Deleting a specific row using Excel VBA

Sub DeleteRow1()
Rows(10).Delete
End Sub

In this program, the 10th row is completely deleted from the active worksheet. Name of the subroutine is DeleteRow1(). Rows(10) refers to row number 10. It has an associated function delete, which when executed deletes the relevant row completely.

Deleting Multiple Rows Using Excel VBA

Sub
DeleteRow2()
Rows("10:20").Delete
End Sub

This code is written to delete multiple rows from row number 10 to 20. Rows(“10:20”) refers to the row range starting from row number 10 to 20. Next let’s move to more advanced programs using if conditional statement and loops.

Deleting Rows Based on Certain Condition Using Excel VBA

Sub
Deleterow3()
Dim lRow
lRow = 100
Do While lRow >= 1
If Cells(lRow,1) Mod 3 = 0 Then Rows(lRow).Delete
lRow = lRow - 1
Loop
End Sub

In this program, we use the Dim keyword to declare the variable lRow, to refer to the last row. lRow is assigned the value 100.   Then we use the ‘Do While’ loop to iterate through the rows starting from row number 100 backwards to row number 1. In each loop, the value at cell with row number equal to lRow and Column 1 is divided by 3. If it’s completely divisible by three, that row is entirely deleted. Next we decrement lRow by 1. We loop on this till the condition evaluates to false.

Delete Entire Rows Where the Cell Type is a String

Sub DeleteRow4 ()
Dim rng As Range, cell_search As Range, del As Range
Set rng = Intersect(Range("A1:C20"), ActiveSheet.UsedRange)
For Each cell_search In rng
If (cell_search.Value) = "Apple"
Then
If del Is Nothing Then
Set del = cell_search
Else: Set del = Union(del, cell_search)
End If
End If
Next cell_search
On Error Resume Next
del.EntireRow.Delete
End Sub

In this program, we declare three variables of data type Range. Here Range(“A1:C20”), returns the selected range of cells. ActiveSheet.UsedRange refers to the selected worksheet.  The intersect function returns the range object that is stored in the variable rng.  We use the “for loop” to iterate through the cells. In this program we have assigned del as the cell object when the cell value is equal to “Apple”. If the value is not equal to “Apple”, then the union function is used to unite del with cell_search.  Del.EntireRow.Delete will delete the row which has the del object.

Delete Every Third Row in a Worksheet Using VBA in Excel

Sub DeleteRow5()
Dim X,Y
X = 1
Y = 1
Set Rng1 = Selection
For xCounter = 1 To Rng1.Rows.Count
If Y = 3 Then
Rng1.Cells(x).EntireRow.Delete
Y = 1
Else
X = X + 1
Y = Y + 1
End If
Next xCounter

In this program, we declare two variables X and Y. Value 1 is assigned to both the variables.  The ‘for’ loop goes from the value 1 to the total number of rows in the spreadsheet selection.  X and Y are incremented by 1 whenever, Y is not equal to 3. When Y is equal to 3, the current row is deleted and Y is reset to 1. This process goes on till the last row of the selection is reached.

How to Delete Duplicate Rows Using VBA in Excel

Now we move on to a more complex program which takes large amount of data. Note that you need to be familiar with the application objects, and its important functions and data members. The knowledge of member data and member functions is necessary to master this code. If that sounds like Latin, take a break and hop over to our Ultimate VBA course to learn more about this.

Sub DeleteDuplicateRows6()
Dim R1 As Long
Dim N1 As Long
Dim V1 As Variant
Dim Rng1 As Range
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set Rng1 = Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Columns(ActiveCell.Column))
Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")
N1 = 0
For R1 = Rng1.Rows.Count To 2 Step -1
If R1 Mod 500 = 0 Then
Application.StatusBar = "Processing Row: " & Format(R1, "#,##0")
End If
V1 = Rng1.Cells(R1, 1).Value
If V1 = vbNullString Then
If Application.WorksheetFunction.CountIf(Rng1.Columns(1), vbNullString) > 1 Then
Rng1.Rows(R1).EntireRow.Delete
N 1= N1 + 1
End If
Else
If Application.WorksheetFunction.CountIf(Rng1.Columns(1), V1) > 1 Then
Rng1.Rows(R1).EntireRow.Delete
N1 = N1 + 1
End If
End If
Next R1
EndMacro:
Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr (N1)
End Sub

In this program, we declared R1, N1, V1 and rng1 as variables. The calculation mode is  set to manual and the ScreenUpdating status is set to false. vbNullString constant represents a null pointer to a string. It stands for empty string. If the count of the rows which have the same content is more than one, in other words, if we have duplicate rows then one row is deleted. N1 is incremented by 1 and the row is also incremented. This loop is executed till all the duplicate rows are deleted. The MsgBox() is used to return the number of duplicate rows deleted which is stored in the variable N1. Cstr() converts a value to a string. Note that at the end of the program the calculation mode is set back to automatic and the ScreenUpdating status is set to true.

VBA is fun to play with, provided you know the basics. Hopefully this tutorial gave you a good background on that. At any time, if you’d like to learn more, do feel free to flex some VBA muscle with MrExcel, or delve deeper into the Excel VBA world with this two part course (basic and advanced).