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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
So are you basically saying with your example you would want the macro to leave you with:

=average(a1,a2,a4)
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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)
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939

ADVERTISEMENT

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.
 

jkharmer

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

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764

ADVERTISEMENT

How bout this?

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

jkharmer

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

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

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
Top