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.
For all the examples we use in this tutorial, here are some generic instructions:
- First open an existing Excel workbook which has data.
- Press Alt+F11 simultaneously which will open the VBA editor.
- Then insert a module using Insert menu.
- 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.
- 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).