Example: Closing Up a Table
Let us consider another example of the power of programming when working with tables. One of the tasks that I find myself needing now and then is a way to remove the contents of a cell from a multicolumn table and “close up” the table. For instance, consider the table in Figure 16-7.
Figure 16-7. A multicolumn table with a duplicate entry
Unfortunately, two cells contain a 5. To remove the entry in row 2 and column 2 and close up the table requires manually moving all of the subsequent entries up one (by columns), to produce the table in Figure 16-8.
Figure 16-8. A multicolumn table with the duplicate cell removed
However, a little program could do that automatically on any size table! The code to do this is shown in Example 16-2. It requires that the cursor reside within the cell to be removed. Note the error-handling portions of the code, which I discuss next.
Example 16-2. Deleting a Table Cell
Public Sub TableDeleteCell() ' Deletes cell contents of current cell ' and moves cells up (column-major) ' Caution: Table must not have merged cells Dim rng As Range Dim tbl As Table Dim cRows As Integer Dim cCols As Integer Dim iCurCol As Integer Dim iCurRow As Integer Dim iRow As Integer Dim iCol As Integer Dim CurCell As Cell On Error GoTo ErrorTableDeleteCells ' ...