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 3^rd row of the 2^nd 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:

VBA, Word Table Insert/Remove Rows/Columns

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:

• Inserting rows in VBA