Archive of Mr Excel Message Board

Back to General Excel archive index
Back to archive home

delete blank cells and shift data up - goto next???

Posted by Kevin on January 25, 2002 2:58 PM
I have a worksheet with 16 columns of numeric data, there are 1547 lines include with the 16 columns. Scattered throughout this worksheet are blank cells. Is there a way to go through and remove only Blank cells and shift cells up, through each of the 16 columns? If this is not clear let me know and I will try to better define my issue

Re: delete blank cells and shift data up - goto next???

Posted by Tom Urtis on January 25, 2002 3:45 PM

As you have probably guessed, there are macros that can be written to do this, but before that, you may want to consider keeping it simple, by trying one of the following non-VBA approaches (this coming from a VBA fan mind you):

(1) Sort the range by a given column, and all the blank cell'd rows will go to the bottom of the list.

(2) AutoFilter the list for non-blanks (Data > Filter > AutoFilter, then use "Nonblanks" as the criteria).

(3) Select the range, then go to Edit > GoTo > Special, select Blanks, hit OK, then Edit > Delete > Entire Row > OK.

If you want to have this automated, let us know, but see if this manual approach works for what you need.

Tom Urtis

I went with Option 1 - thanks it works great.

Posted by Kevin on January 25, 2002 3:50 PM

This archive is from the original message board at
All contents © 1998-2004
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.