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

 

[Rick Rothstein]

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

There is no need to hop around all over the place. Instead of activating sheets back and forth, you can simply reference them directly. All of the code above can be replaced by this (note my method for calculating the last row with data in it... it does not require Fluff's value=value trick).

[table="width: 500"]
[tr]
	[td]Private Sub Save_Click()
  Dim LastRow As Long
  Worksheets("summary").Range("H14:V36").Copy
  LastRow = Sheets("Results").Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
  Sheets("Results").Cells(LastRow + 1, "A").PasteSpecial Paste:=xlPasteValues, Transpose:=True
  Application.CutCopyMode = False
End Sub[/td]
[/tr]
[/table]

 

[Fluff]

@Rick
With your code, cells that had a formula returning "" still return false to =ISBLANK(B7)