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

jkharmer

Board Regular
Joined
Jul 7, 2008
Messages
77
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
So are you basically saying with your example you would want the macro to leave you with:

=average(a1,a2,a4)
 
Upvote 0
Maybe try this:

Code:
Sub removeRefError()
Range("E:E").SpecialCells(xlCellTypeFormulas, 16).Replace What:="#REF!", Replacement:=""
End Sub
Change column E to the correct column or ranges you want to work with.

Hope that helps.
 
Upvote 0
Maybe try this:

Code:
Sub removeRefError()
Range("E:E").SpecialCells(xlCellTypeFormulas, 16).Replace What:="#REF!", Replacement:=""
End Sub
Change column E to the correct column or ranges you want to work with.

Hope that helps.
Running this code on the formula:
=AVERAGE(A1,A2,#REF!,A3)

results in the formula:
=AVERAGE(A1,A2,,A3)
which, when calculating the average, still thinks there are 4 cells.

(The code also errors if there are no such error cells)
 
Upvote 0
Thanks Peter I did not test that well enough as I used sum as my test point and I don't know why. Then in that case it would be a lot more complicated than my simple solution as you would need to replace the commas also. Maybe I'll try coming back to this tomorrow if I have time.
 
Upvote 0
Thansk for the replies - that was the problem I foresaw, getting rid of the commas. Could I not run the macro twice, one with the comma and one without?
 
Upvote 0
How bout this?

Sub Remove_Ref()
Range("A1") = Replace(Range("A1").Formula, "#REF!,", "")
End Sub
 
Upvote 0
Thanks njmack,

Does work, but only if you set the cell to look in, seems to error if I tell it to search for every occurence in the whol of the sheet.

Any ideas on how to get it to search all cells and then perform this replacement when it finds a #REF!.

Much appreciated, James
 
Upvote 0
Maybe try this:

Code:
Sub removeRefError()
On Error Resume Next
Range("E:E").SpecialCells(xlCellTypeFormulas, 16).Replace What:="#REF!,", Replacement:=""
End Sub
The only problem you will face with this is if the #REF is the last item in the cell because it will not be followed by a comma. So you may want to modify to this:

Code:
Sub removeRefError()
On Error Resume Next
Range("E:E").SpecialCells(xlCellTypeFormulas, 16).Replace What:="#REF!,", Replacement:=""
Range("E:E").SpecialCells(xlCellTypeFormulas, 16).Replace What:="#REF!", Replacement:=""
End Sub
Hope that helps.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top