MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


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

Kevin,

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

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

I went with Option 1 - thanks it works great.