Paste Values empty cell, not empty

That is because they are not blank. They contain an empty string, ie. "" or more exactly, they contain your formula which presents visually what looks like an empty cell.

Is it feasible to return 0 instead of "", and then filter out the 0s?

If you click on the cell, does it actually contain "" or does it contain a formula that says display "". I'm thinking the latter.

If this is the case, you may have to do a copy and paste special to get the values in the cells instead of the formulas. Then delete all having the value "".

Originally Posted by alansidman

If you click on the cell, does it actually contain "" or does it contain a formula that says display "". I'm thinking the latter.

Test this with a simple formula that returns "". Copy and paste the value, and then run =isblank on the new cell where you pasted the value. You get FALSE.

Originally Posted by warston

on the sheet where I have pasted values it does not show as anything, either in the cell or in the formula bar.

Correct, but the cell is not blank. This is easily verified.

Can you return a different value with the formula that you can then filter out? Return 0, or "Filter Out", or "Ignore me" or something of the sort?

Try using autofilter and then sort --> delete all the 0s in one batch?

Here is a short macro that will look at your range of values and if it finds "", it will clear the contents of that cell.

Please Login or Register  to view this content.

I am not sure what you mean by delete the cell and this is the closest I can come to make that happen without physically deleting the cell, which would move all the data around. I don't think that is what you meant.

I think he's looking for c.entirerow.delete, if a VBA solution is the route he elects to go.

I think your best bet is to use the autofilter and sort, and then manually delete them in a large batch. Not nearly as quick as a VBA solution, but effective nonetheless.

I agree with Warston's post #15 if you are looking to go the non-vba route. If you change your mind and want the VBA route, post back and we can modify the example given.