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.
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 .
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.
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.
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.
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.
And here’s a single line of code to accomplish the same thing:
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.
Delete rows if the first column is blank
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
Delete a row if it is completely blank
' 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
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.