Macro to remove the #REF! generated after deleting rows?

[jkharmer]

Hi,

Does anyone know whether it is possible to create a smart macro which will remove all references to the #REF! which is left when rows are deleted. It would have to remove all trace of it from any equation it may be in (i.e. if it was in an averaging equation, it would need to remove the preceeding comma as well:

=average(a1,a2,!#REF!,a4 .........)

Thanks in advance, James

 

[jkharmer]

May have got it, that one! Will have to try a few more "what ifs ...", but could be sorted.

Thanks, James

 

[Joe Was]

Sub AllRefErrors()
'Standard code module, like: Module1.
'Fix active sheet reference errors, by deleting the bad formula references.
Dim lngLstRow&, lngLstCol&
Dim rngMyRng As Range

On Error Resume Next

With ActiveSheet.UsedRange
lngLstCol = .Columns.Count + .Column - 1
lngLstRow = .Rows.Count + .Row - 1
End With

Set rngMyRng = ActiveSheet.Range(Cells(1, 1), Cells(lngLstRow, lngLstCol))

rngMyRng.SpecialCells(xlCellTypeFormulas, 16).Replace What:="#REF!,", Replacement:=""
rngMyRng.SpecialCells(xlCellTypeFormulas, 16).Replace What:="#REF!", Replacement:=""
End Sub