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.
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 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.