Thanks for that. I worked that out after 5 go's of going what is wrong
with the last row??
Using the code which you supplied has given me an idea for the stage
before I get to remove the empty cells. Would you mind if I picked
your brain??? I think it could hopefuly be changing a few lines of
your code which you supplied.

My data in orginal format looks like this:
A B-j(all blank) K L
yyyy xxxx xxxx
yyyy xxxx xxxx
yyyy xxxx xxxx
yyyy xxxx xxxx
yyyy xxxx xxxx
yyyy xxxx xxxx
yyyy xxxx xxxx

I then run a macro which is slow (i thinking large calculation doesn't
help) which finds if there is a value in K, put in the formula
"=VLOOKUP(RC[1],setnode!C4:C5,2,FALSE)" in cells B-J.
I then value it and change the #n/a's to blank. Can you help? Sorry to
be a pain :-) I like the lastrow you use instead of determining a
setting a range as i do.

Thanks again,
Marc

Sub SAP_hier1()


Application.ScreenUpdating = False
On Error Resume Next
Dim rngcell As Range

Sheets("SAP").Activate
Range("K2:K25000").Activate
For Each rngcell In Selection
If rngcell < blank Then

rngcell.Offset(0, -1).FormulaR1C1 =
"=VLOOKUP(RC[1],setnode!C4:C5,2,FALSE)"
rngcell.Offset(0, -1).AutoFill
Destination:=Range(rngcell.Offset(0, -9), rngcell.Offset(0, -1)),
Type:=xlFillDefault

Range(rngcell.Offset(0, -9), rngcell.Offset(0, -1)).Copy
Range(rngcell.Offset(0, -9), rngcell.Offset(0, -1)).PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range(rngcell.Offset(0, -9), rngcell.Offset(0, -1)).Replace
"#N/A", Null


Else
End If
Next rngcell

Application.ScreenUpdating = True
End Sub

JWolf wrote in message ...
Change line:
lastRow = Cells(Rows.Count, "A").End(xlUp).Row - 1 'Find last row
to:
lastRow = Cells(65536, 1).End(xlUp).Row 'Find last row

missed last line otherwise.