That code runs OK and doesn't destroy the formula on my computer.

Could you step through each line (use the VBA debugger, press F8 to step
through the lines)
Make sure the screenupdating is turned on and you can see when and which
line corrupts your formula.

Rob


"Sauron " wrote in message
...
Cool, cheers for the responses!

I don't mean to ignore you. I only get a couple of hours each night to
answer questions.

Noo! I wasn't being impatient, I was just a little sad at my own
efforts when I still couldn't get it working, sorry! it wasn't directed
at you! I know there's alot of posters I'm glad of any reply...

Code I'm running;

Sub RemEmptyFinal()

Application.ScreenUpdating = False
Dim rng As Range, ix As Long

'Sheet1

Application.Calculation = xlCalculationManual
Sheets("AOwens").Select
Sheet1.Range("W2:W1000").Select
Selection.Copy
Sheet1.Range("U2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False
Set rng = Intersect(Selection, ActiveSheet.UsedRange)
If rng Is Nothing Then
GoTo done
End If
For ix = rng.Count To 1 Step -1
If Len(Trim(Replace(rng.Item(ix).Formula, Chr(160), ""))) _
= 0 Then rng.Item(ix).Delete (xlUp)

Next

~~~~~~~~~~~

This;


=IF(-T2-="","",IF(Q2="","",IF(O1Q1,"",IF(O1<=-T2-,S2,IF(Q1S2,MATCH(O1,-T2:
T1000-,1),"")))))

=IF(T3="","",IF(N2=$R$1,"",IF(N2=$Q$2,"",N2+1)))

Becomes;


=IF(#REF!="","",IF(Q2="","",IF(O1Q1,"",IF(O1<=#RE F!,S2,IF(Q1S2,MATCH(O1,T2
:T472,1),"")))))

=IF(#REF!="","",IF(N2=#REF!,"",IF(N2=$Q$2,"",N2+ 1)))

Any formula directly referencing the replaced range becomes #REF! it
seems to be at random so could it be because the cell is in transition?
I don't know I'm confused...

If I just have the Vlookup or Match statement it does it just to its
formula as well.

Thanks for all the replies all, appreciated.

Cheers,
Sau


---
Message posted from http://www.ExcelForum.com/