Detect whether a table cell is empty


Detect whether a table cell is empty

Article contributed by Bill Coan

Method 1

Use the range object to detect empty cells based on the idea that an empty cell consists of a paragraph mark followed by Chr(7).

Sub CheckTableCells()

Dim oCell As Cell
Dim oRow As Row

For Each oRow In Selection.Tables(1).Rows
    For Each oCell In oRow.Cells
        If oCell.Range.Text = Chr(13) & Chr(7) Then
            MsgBox oCell.RowIndex & " " & oCell.ColumnIndex & " is empty."
        End If
    Next oCell
Next oRow

End Sub

Method 2

Use a range variable, set it to mark each cell's range; move the end of the range so that the end of cell marker and paragraph marker are not included in the range; (to do this you only have to move it by one character); and get the text within the range. If the cell is empty there will be no text within the range

Sub CheckTableCells()

Dim oCell As Cell
Dim oRow As Row
Dim MyRange As Range

For Each oRow In Selection.Tables(1).Rows
    For Each oCell In oRow.Cells
        Set MyRange = oCell.Range
        MyRange.End = MyRange.End - 1
      If Len(MyRange.Text) = 0 Then
            MsgBox oCell.RowIndex & " " & oCell.ColumnIndex & " is empty."
        End If
    Next oCell
Next oRow

End Sub

Method 3

Here's some code that is similar, but which selects each cell before announcing whether it is empty.

Sub CheckTableCells()

Dim oCell As Cell
Dim oRow As Row
Dim MyRange As Range

For Each oRow In Selection.Tables(1).Rows
    For Each oCell In oRow.Cells
        If Selection.Text = Chr(13) & Chr(7) Then
            oCell.Select
            MsgBox oCell.RowIndex & " " & oCell.ColumnIndex & " is empty."
        End If
    Next oCell
Next oRow

End Sub