Paste Special Values Question

[dazfoz]

Hi,

Thanks to everybody who helped yesterday, but now I have another question.

I'm using a feeder page with the following formula to populate;

=IF('Data Sheet'!$A2="","",'Data Sheet!I2)

So if the cell A2 on the data sheet is blank (If A2 is blank, then the rest of A will be blank) then the cells in question will have no entry (using double speech marks to do this).

Then I have a Macro that copies the whole feeder sheet and drops it into a sheet with paste special values.

My logic was that pasting the values would infact leave the cells with ("") blank, with no entry whatsoever. But if I go to this paste special sheet, and hold ctrl & Down on the arrow key, instead of going to the last cell with data in it, it goes to the last cell where the formula was. Yet clicking on the cell there is no nothing in it, no formula, no spaces, no nothing!

Its driving me wild, as I need the ctrl & down, to go to the last line of data so that I can paste some more info by Macro.

Any help would be appreciated.

Regards

Daz.

 

[JoeMo]

As you have discovered, when you copy a cell containing "" and paste its value to another cell, Excel does not consider the destination cell to be empty (blank) even though it appears that way when you select it.

You could try this on the pasted selection (destination range) immediately after the pastespecial:

For each c in Selection
   if c.value = vbnullstring then
      c.clearcontents 
   end if
next c
'rest of your code