Empty cells vs blank cells - how to change efficiently

[seriousnick]

Hi
I have "iferror" lookup formulas (formulae? ) in 31 columns and down 25,000 rows. (IFERROR then return "")
I then copy and paste as values.
I then eventually move all results (1 per row) to the lefthand column of the 31.
I use special cells for efficiency (goto special>blanks, then delete shift left). However, as I have pasted as values, the cells are not actually "blank" (null?) as I have to run VBA code first:

If cell = "" Then cell.ClearContents

Although this does the job, because it loops through 775,000 cells it takes a while.
Is there a better way to do this either from the iferror part of the lookup, or from copy/paste as values or even the VBA?

Many thanks for any help.
Nick

 

[seriousnick]

Update:

If I change my formula from

=IFERROR(VLOOKUP(A1,OTC!$A$2:$A$100,1,FALSE),"")

to

=IFERROR(VLOOKUP(A1,OTC!$A$2:$A$100,1,FALSE),)

then this returns zero's. Search and replace these and I have blanks that can be seen by special cells.
(However s & r takes a while too!)

Thanks
Nick

 

[HalfAce]

Rather than search / Replace the zeros with blanks, and then deleting the blanks, can't you just autofilter the column for zeros and then delete the filtered rows?

With 3/4 of a million rows to work on I doubt there will be many things you can do that will happen fast, but this might cut the actions required by about half.

Hope it helps.

 

[Rick Rothstein]

Hi
I have "iferror" lookup formulas (formulae? ) in 31 columns and down 25,000 rows. (IFERROR then return "")
I then copy and paste as values.
I then eventually move all results (1 per row) to the lefthand column of the 31.
I use special cells for efficiency (goto special>blanks, then delete shift left). However, as I have pasted as values, the cells are not actually "blank" (null?) as I have to run VBA code first:

If cell = "" Then cell.ClearContents

Although this does the job, because it loops through 775,000 cells it takes a while.
Is there a better way to do this either from the iferror part of the lookup, or from copy/paste as values or even the VBA?

In VBA, you can take the range that encompasses all your cells, let's call it BigRange (a range reference or a variable declared as Range) because I am unsure what the range's address is, and do this...

With BigRange
  .Value = .Value
End with

This will convert "" to an empty cell. The reason you restrict it to the range in question and not all cells is because that code will also turn any formulas into constants.

 

[seriousnick]

Thanks for prompt reply.
Its only cells I am deleting (not actual rows) so I can shift all the values to the one column.
Cheers
Nick

 

[seriousnick]

Thanks Rick for quick reply.
I am trying to avoid VBA if possible.
I would ideally like to use something in the formula that will return a blank cell if there is no lookup value to return.
Is this possible?
Cheers
Nick

 

[Rick Rothstein]

I would ideally like to use something in the formula that will return a blank cell if there is no lookup value to return.
Is this possible?

No, it is not possible... formulas can only return values to the cell they are in... they cannot clear the value of a cell. Think about it... if they could clear the contents of the cell, then they would, in effect, be able to erase themselves. How useful would that be if you had a formula referencing a cell and accidentally put a value in that cell which caused the formula to erase itself?

 

[seriousnick]

With BigRange
  .Value = .Value
End with

Thanks again Rick - I used your suggestion in VBA in the end against null cells rather than zeros (much much quicker than search/replace).
Many thanks for your help.
Nick

 

[seriousnick]

No, it is not possible... formulas can only return values to the cell they are in... they cannot clear the value of a cell. Think about it... if they could clear the contents of the cell, then they would, in effect, be able to erase themselves. How useful would that be if you had a formula referencing a cell and accidentally put a value in that cell which caused the formula to erase itself?

Yeah I see that.
What I suppose I meant was for the formula to return a result that when I pasted and copied as values it would then be a blank cell.
Thanks once again.

 

[Rick Rothstein]

Yeah I see that.
What I suppose I meant was for the formula to return a result that when I pasted and copied as values it would then be a blank cell.
Thanks once again.

If you want to do it manually, you can copy the cells and then use PasteSpecial/Values to put to what you are describing you want to do.