Blank cells as result of formula with ""

[Joe78man]

Hello guys,

I'm making a macro that finds the last empty cell and paste from there, the problem I'm having is that Excel won't consider an empty cell when copying a cell which formula's result is "" and pasting values, hence, the macro leaves as many empty cells as where copied from blank cells (as result of a formula)

It's kinda hard to explain so I will ilustrate:

Cell A1:
=IFERROR(INDEX(Summary!$AA:$AA,MATCH(COUNTA($H$14:H14),Summary!$AK:$AK,0)),"")
let's say there is an error and the cell value is ""

Macro button would copy A1 and paste values at B1 (just to ilustrate)
B1 looks empty, but when I use ctrl+arrows to move it stops there as if it had an actual value. I tried skipping blanks but it seems excel considers "" as a value and not null, so when pasting it pastes the same 'value' and when moving across the sheet it behaves as if the cell had a content.

Do anybody know a work around?

Thanks

 

[mumps]

Do you want to find the last empty cell in a row or in a column?

 

[dfsobral]

in the macro you could try to place an if statment

if a1 value equal "" then do nothing

this way the macro wouldn't do anything to column B and you should not have a problem

 

[Fluff]

Hi & welcome to MrExcel.
Could you supply the macro?

 

[Joe78man]

Maybe I was not clear. The macro just finds the last empty cell, the macro is not the problem, the problem is that if you have a formula like this

=if(2>1,"true","") <- Mind the "", that is the result I mean

You will see the cell empty as result of the formula, but if you copy that cell and paste the VALUES on another cell, the destination cell is not actually empty. Given my macro finds the last empty cell, it will consider the "" pasted as value as a cell with a value.

 

[Joe78man]

Do you want to find the last empty cell in a row or in a column?

I know how to do it, the problem is how excel considers "" as a result of a formula. As far as I can see it is not actually empty, instead it seems to have the value="" and when you use ctrl and the arrow keys to move it will behave as if it had an actual value, meaning that using ctrl+arrows to move won't work to find the last cell with value (either in a column or a row)

 

[Joe78man]

Hi & welcome to MrExcel.
Could you supply the macro?

Private Sub Save_Click()
'Boton SAVE
'copia el resultado de todas las evaluaciones en la tab 'Results' debajo de la ultima entrada existente
Worksheets("summary").Range("H14:V36").Copy
Sheets("Results").Activate
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True
Sheets("Summary").Activate
Application.CutCopyMode = False


End Sub

 

[Fluff]

Add this line

Selection.PasteSpecial paste:=xlPasteValues, Transpose:=True
[COLOR=#0000ff]Selection.Value = Selection.Value[/COLOR]
Sheets("Summary").Activate

Your cells should now be blank

 

[Joe78man]

Add this line

Selection.PasteSpecial paste:=xlPasteValues, Transpose:=True
[COLOR=#0000ff]Selection.Value = Selection.Value[/COLOR]
Sheets("Summary").Activate

Your cells should now be blank

Just perfect!, thank you so much.

Would you mind explaining to me what that magical line does? I suppose what it does, but I'd like to know the logic behind it to use it in any other context

 

[Fluff]

When you paste as values & a cell has a formula that returns "", the paste command has to paste something,so it inserts a NullString.
But when you do .Value=.Value it removes any NullStrings as they don't have a value.