Compacting rows

Posted by Cliff on September 13, 2001 8:29 AM

I have a lot of data like this, where "" means the cell is blank. I want to eliminate all the blank cells in each row from ColD to the first cell that contains data. The data are always in contiguous blocks.

ColA ColB ColC ColD ColE ColF ColG ColH ColI
----------------------------------------------------------------------------
Charles H NL "" "" 1997 1998 1999 2000
James N AL "" 1999 MLB 2000 "" ""
Richard G NL "" "" "" 1997 1998 ""

In other words, the data should be in this form:

ColA ColB ColC ColD ColE ColF ColG
----------------------------------------------------
Charles H NL 1997 1998 1999 2000
James N AL 1999 MLB 2000 ""
Richard G NL 1997 1998 "" ""

I tried a macro by recording key strokes, but it wouldn't work properly.

Thanks,
Cliff

Posted by Mark W. on September 13, 2001 8:35 AM

1. Select your data range.
2. Choose the Edit | Go To... Special... Blanks
menu command and press [ OK ].
3. Choose the Edit | Delete... menu command opting
to "Shift cells left" and press [ OK ].

Posted by Cliff on September 13, 2001 12:16 PM

Wow, was that easy; I had no idea. I was expecting to learn that I needed VBA script.

Thank you very much,
Cliff