• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Deleting blanks in excel. Excel not 'seeing' my blanks as blanks

sassystat

New Member
I posted last week & was delighted to be able to delete blank cells in excel using these instructions: http://chandoo.org/wp/2010/01/26/delete-blank-rows-excel/


However, after manipulating data differently (using an 'if then' to create new values based on existing one, excel now tells me 'no cells are found' in a pop-up. I don't see anything in the cells and copied and pasted only values.
 
Unfortunately, when you use a formula to make a blank cell like this:

=IF(Criteria,Something,"")

The cell isn't "blank" according to the Special cells method (this can get really annoying, since when you copy/ paste values to somewhere else, it looks EXACTLY LIKE A BLANK CELL. (personal aggravation).


What I typically do to get around this is force an error and then look for that, like:

=IF(Criteria,Something,1/0)

And then do a Special Cells, formula - error.


Or, you can have it be something unique like:

=IF(Criteria,Something,"zzzz")


Then, do a Find All for "zzzz". In the drop down of the Find dialogue, hit Ctrl+A to select all the cells, and then switch to the workbook and hit Ctrl and the minus key to delete all those cells.
 
Select the range in Question and do:

Data | Data Tools | Text to Columns without specifying any delimiters and finish it. Now the method will work as intended.
 
Back
Top