Archive of Mr Excel Message Board
Back to Edit in Excel archive index
Back to archive home
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
Re: Compacting rows
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 ].
Re: Compacting rows
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
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our
online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.