If you always want a formula to point at T2 (say), and sometimes you're going to
delete T2, you could wrap it in =indirect().

=if(indirect("t2")="",.....

(I didn't understand what those -T2- meant in your formula, though.)

Another option.

change all your formulas to text, do your processing, then change them back to
formulas.

Option Explicit
Sub testme()
With Worksheets("sheet2")
.Cells.Replace what:="=", replacement:="$$$$$=", lookat:=xlPart
End With

'do your clean up code.

With Worksheets("sheet2")
.Cells.Replace what:="$$$$$=", replacement:="=", lookat:=xlPart
End With
End Sub

(This method won't work if you really wanted formulas to adjust, though.)

"Sauron <" wrote:

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/

--

Dave Peterson