Delete cells and shift up

**smiso24** · 11-07-2009, 01:58 PM

I have data in cells A1:FM1326 I want to delete all of the cells that say FALSE and shift the remaining cells up. How do I do this? Thanks.

**JBeaucaire** · 11-07-2009, 02:13 PM

Are there formulas in those cells giving answers or FALSE? I hope so.

When the formulas in those cells return values, are they numeric? I hope so.

First..change your formula so that it doesn't return FALSE, but returns an actual TEXT string like "FALSE" or "No".

Then you can use a special-select method to select all the text cells at once.

1) Highlight the range
2) Press F5 and click on Special
3) Select Formulas > Text only
4) Click OK

Now all the "FALSE" or "No" cells are highlighted.

5) Click on Edit > Delete

Those steps can be recorded into a macro very easily if the values in the range are such that you can use this technique.

**smiso24** · 11-07-2009, 02:20 PM

Yes, there is a formula that yields the FALSE value. The other values, however, are NOT numeric. What do I do?

**JBeaucaire** · 11-07-2009, 02:41 PM

Post up a sample workbook so I can take a look. Hopefully there's a quick way, if not, then a macro would have to loop through every cell and evaluate. Yuck.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

**smiso24** · 11-07-2009, 03:12 PM

Actually, I got it! I found and replaced all the FALSE cells to blank cells and then used that F5 command you gave to delete all the blank cells and shift up. Thanks!

**JBeaucaire** · 11-07-2009, 03:51 PM

AH, great. I love the Special Cells feature...very useful, eh?

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)