Excel doesn't think blank cells are not really blank?

Here is a peculiar problem:

I have a macro, which copies a table to a new worksheet and pastes all as values. I then need to delete the rows, which contain one or more missing values (i.e. rows containing blank cells.). However, when I use this excellent macro:

Sub DeleteBlankRows2()
'Deletes the entire row within the selection if_
some of the cells WITHIN THE SELECTION contain no data.
On Error Resume Next
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

or use the GoTo Blanks Cells function, nothing happens. Excel does not seem to think that the blank cells are actual blank. But after having pressed delete in a blank cell, then that cell is accepted as blank.

What do I do about that? Has anyone ever experienced this?

Thank for your help!

Leon

This problem does creep up here frequently in one form or another.

the xlblanks special cell refers to a cell if and only if that cell contains Nothing. A cell that contains an empty string ("") or a space (" ") may look empty, but it still contains something. A cell that contains a formula that returns an empty string [=if(iserror(a1),"",a1)] contains something and will not be included in the collection of blank cells.

What you need to do is go back to your spreadsheets and determine what those "blank" cells contain, so you can modify that macro to search for those cells.

Thanks a lot for your reply! I did not know that.

So, the formulas that return the blank cells actually return "". Do you know by any chance how I could change the macro above to search for that instead?

Thanks again!

This may not be syntactically correct, so adjust as needed, but something along the lines of:

Please Login or Register  to view this content.

As noted, that code probably needs a little more work to get it to work properly, but it should give you the idea.