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

Paste Special - Values only not working as expected

D

dnessim

Member
Hi,
I have a formula in a worksheet that I copied down aprox 61K rows.
The formula looks like this:
=IF($A4=$H$1,OFFSET($A4,-1,1,1,1),"")
so when this formula is false I get several "blank" rows.
When I copy this column to another sheet and try to paste special values only, I see my blank rows but they are not really blank ? My ultimate goal is to delete all my blanks.
What can I do different to get the blanks removed.
Thanks
Dave
 
Dave
The formula appears fine, Can you post the workbook so we can see what is wrong ?
 
I copied the data to another sheet and used paste special > values , then I used filter to get rid of the blanks.
This worked so I am all set.
Still dont understand why my other method used find/replace > go to special > blanks and I couldnt find any blanks even though i had some blank cells.
Thanks
Dave
 
You have found one of the the annoying little quirks in XL. You are correct, the "" is in the cell, and counts as a zero-length text non-blank cell :confused:. This makes it very annoying when trying to use Special Cells to delete those cells. My workaround is to purposely generate an error, and then delete the errors, like so:
=IF($A4=$H$1,OFFSET($A4,-1,1,1,1),1/0)
this will create a #DIV/0 error, and after you do the paste values, you can do a Go to - Special Cells - Constants:Errors, and delete them.
 
Luke, you are right its anoying since I had the values I wanted completed in about 20 mins then I spent the next few hours searching and tshooting to try to get rid of the "empty" cells. I did get to converse with all you fine folks about excel, which is always a pleasure. I did solve my problem by using the filter which I was able to filter on "blanks" that did get rid of my blanks. I will file away your solution for the future.
Thanks
Dave
 
You must log in or register to reply here.
Back
Top