Excel Annoyances

IMPORTING DATA ANNOYANCES

COPYING A WORD TABLE INTO EXCEL 97 INTRODUCES BLANK ROWS

The Annoyance:

When I copy a table from a Word document into an Excel 97 worksheet, Excel insists on assigning each cell in the Word table to two cells in the worksheet (shown in Figure 1-7). Worse yet, it merges some of the cells. Please tell me there’s some way I can stop Excel from merging the cells! If not, can I at least undo the merges and delete the resulting blank rows after I paste my table?

Figure 1-7. For some reason, you get two rows for one when you bring a Word table into Excel 97.

The Fix:

David and Raina Hawley, the authors of Excel Hacks: 100 Industrial-Strength Tips and Tools (O’Reilly), wrote a great macro that removes blank rows from a selection. I added a section at the top of the procedure to remove text wrapping and cell merges from the imported list so that each row will be separate, allowing Excel to remove the blank rows. This macro assumes the data you imported is selected. If the list isn’t selected, click any cell in the list, press Ctrl-∗ to select the list, and run this macro to clean up your data:

 Sub FixWordTableInExcel97() 'Removes all cell merges and text wrapping from 'the pasted table and then deletes all blank 'rows added by the paste. With Selection .WrapText = False .MergeCells = False End With Dim Rw As Range With Application .Calculation = xlCalculationManual .ScreenUpdating ...