How to Delete Blank Rows from a Word Table Using VBA
This article shows how to use VBA in MS Word to delete empty rows from a table or rows that have at least one cell that is empty. Say you have multiple tables in a Word document and you want to print the document with only the non-empty rows. Or the tables are to be imported into a database in which the values cannot be blank. These are just some of the scenarios where you will find a practical implementation of this code.
Before we jump into the examples, let us look at two different ways to access a table in a Word document.
1. By using the table index. Below code sets the Tbl variable to the second table in the document.
Set Tbl = ThisDocument.Tables(2)
2. By using the table title. You can set the title by selecting a table in word > Right Click > Table Properties > Alt Text tab
You can loop through all the tables in the document and check for the title to update only those tables that specify a certain condition:
For Each Tbl In ThisDocument.Tables If InStr(1, Tbl.Title, "Invoices") = 1 Then 'Your code goes here End If Next Tbl
So, the code will be executed for all the tables that have title starting with invoices.
Example 1: Delete rows that are completely blank
For each row, the code checks the length of the row and if it is equal to the length of a blank row, it is deleted.
Note that we start from the bottom-most row and move to the top (because the row index will change if we start deleting from the top).
Set Tbl = ThisDocument.Tables(1) With Tbl noOfCol = Tbl.Range.Rows(1).Cells.Count For i = .Rows.Count To 1 Step -1 With .Rows(i) If Len(.Range) = noOfCol * 2 + 2 Then .Delete End With Next i
Here, the “Table.Range” property returns a Range object that represents the portion of a document that is contained within the specified table.
.Rows(1) returns the first row of the table
.Cells returns the cells in the selected row
.Count gives the number of cells in the row (i.e. number of columns in the table)
.Delete deletes a row
A point to be noted here is that the length of each blank cell in the table is 2. And if you have any text in the cell, the length of the cell equals length of the string + 2. For example, if a cell contains text “Word”, length of the cell is 6 (4 + 2)
Also, the length of the end of row character is also 2. So, length of a blank row equals 2* + 2
Example 2: Delete rows that contain at least one cell blank
Here the loop iterates through each cell in the table and if the length is equal to 2 (i.e. the cell is empty), the entire row is deleted. This example and the next one use the Mod operator.
Set Tbl = ThisDocument.Tables(1) With Tbl noOfCol = Tbl.Range.Rows(1).Cells.Count With .Range For i = .Cells.Count To 1 Step -1 On Error Resume Next If Len(.Cells(i).Range) = 2 Then .Rows(.Cells(i).RowIndex).Delete j = i Mod noOfCol If j = 0 Then j = noOfCol i = i – j End If Next i End With
Note that “j” is used here to adjust the value of “i” to the row above the deleted row (to reduce the number of executions of the loop). If your table consists of merged cells, it is better to remove the three lines after the delete row function as it will cause errors in the output.
Example 3: Deleting rows from a table with headers
Consider a table that has three header rows with blanks in them:
The first 3 lines consist of the headers and obviously we do not intend to delete them even if there are blanks present. So, we modify the above code to accommodate for the first three header rows. Here is the entire code:
Sub DeleteEmptyTableRows() Application.ScreenUpdating = False Dim Tbl As Table Dim i As Long Dim noOfCol As Integer, j As Integer For Each Tbl In ThisDocument.Tables With Tbl noOfCol = Tbl.Range.Rows(2).Cells.Count For i = .Rows.Count To 4 Step -1 With .Rows(i) If Len(.Range) = noOfCol * 2 + 2 Then .Delete End With Next i With .Range For i = .Cells.Count To noOfCol * 3 + 1 Step -1 On Error Resume Next If Len(.Cells(i).Range) = 2 Then .Rows(.Cells(i).RowIndex).Delete j = i Mod noOfCol If j = 0 Then j = noOfCol i = i - j End If Next i End With End With Next Tbl Set Tbl = Nothing Application.ScreenUpdating = True End Sub
Things to note:
– The first row consists of merged cells. So, we have used the second row to get the number of columns in the table
– The first for loop that checks for an entire empty row goes from the last row to row number 4
– For the second for loop that checks each cell, the counter ends at the first cell of the fourth row
– I have used both the for loops just to demonstrate how to take care of headers in both the examples. You can choose which one to use based on your requirement.
After you run the code, the table will look like this: