How to Delete Rows Using VBA
Contents
Why would you want to delete a row?
Rows are the horizontal layers of data (that could also be blank) in any table. Worksheets in MS Excel are actually all considered to be tables. In MS Word, tables can be inserted into documents for your use.
Rows are available for us to manipulate in both host applications using VBA. When we need to remove blank rows or rows with old data from tables, we can use the delete row feature available in the context menu. This same feature is offered by VBA too.
Delete a row from a table in a Word document
Using indices, we first need to reference the table. Then, we can use index another time to refer to the row that needs to be deleted.
For example, if we need to delete the 3rd row of the 2nd table in a Word document, we can use the code below.
Tables.Item(2).Rows(3).Delete
To learn all about this method, please refer to this article:
Deleting a row from a worksheet in MS Excel
Delete row manually
When we want to delete a row manually, we can simply select it, right-click and select the “delete” menu item from the context menu. This action will delete the selected row.
It doesn’t get any easier than that.
Delete row using VBA
There are many ways to delete a row this using VBA. You can either implement simple code just to get the work done for the moment, or create a perfect add-in that can be used by anyone, anytime. Here are some possible ways in which we can delete rows using VBA code.
Method 1 – Rows.Delete
Open the worksheet on which the deletion of rows has to be done. Open the VBA module , type in the code below and run it .
Sub row_deletion_demo() Rows(2).Delete End Sub
This can delete the second row in the open worksheet.
Method 2 – Select and Delete
Here the worksheet is opened like in method 1. The code is split into two parts. The first line selects the row and the second line deletes whatever is selected.
This code selects and then deletes the first row in the open worksheet.
Sub row_deletion_demo() Rows(5).Select Selection.Delete End Sub
Method 3 – Automatically open the worksheet first
Unlike in the two methods above, opening of worksheet doesn’t have to be done manually because you can automate it using the code below.
Sheets("Wonders").Select
This line of code should be inserted in the module before selection and deletion of rows.
Method 4 – Select and Delete Simultaneously
Here is a single line of code that can directly delete the specified row without even opening the worksheet. This can eliminate 2 lines of code or your manual effort when compared to the methods above.
Sub row_deletion_demo() Sheets("Wonders").Rows(5).Delete End Sub
Method 5 – Select a Range
This method again starts with the selection of worksheet, rows and then deletion. But several continuous rows are selected instead of one.
Sub row_deletion_demo() Sheets("Wonders").Select Rows("5:7").Select Selection.Delete End Sub
And here’s a single line of code to accomplish the same thing:
Sheets("Wonders").Rows("5:7").Delete
Method 6 – Delete the ActiveCell
The code below selects the entire row to which a pre-selected, active cell belongs. Then the second line deletes the selected row.
Sub row_deletion_demo() ActiveCell.EntireRow.Select Selection.Delete End Sub
Sample programs
Delete rows if the first column is blank
Sub row_deletion_demo() Rows = InputBox (" Enter the number of rows to be checked for ") ' loop to iterate through rows For i = 1 To Rows ' check if the first column is blank If Sheets("Wonders").Cells(i, 1).Value = "" Then ' delete that row if first column of the row is blank Rows(i).Delete End If Next End Sub
Delete a row if it is completely blank
Sub row_deletion_demo() Dim range1 ' loop to iterate through For i = 1 To 20 ' get the range of row Set range1 = Worksheets("Wonders").Rows(i & ":" & i) ' find the number of blank columns in that row filledcols = Application.WorksheetFunction.CountA(range1) ' delete the row if all columns in it are blank If filledcols = 0 Then Sheets("Wonders").Rows(i).Delete End If Next End Sub
Conclusion
Though it may be easy to delete rows manually, when we want to delete several discontinuous rows, like in our sample programs, VBA comes to our rescue. At the same time, you should remember that any task done through a VBA program cannot be reversed. Manual effort is required to redo or reverse it.
It is easy to delete rows but difficult to re-create them. So cross check the program logic and be sure of what you’re doing before running the macro code.
See also: