Remove Unnecessary Sheet References From Excel Formulas With VBA Macro

Delete Same Tab Formula References From Cell Functions

What This VBA Code Does

Have you ever seen a formula in Sheet1 where it is written with references to Sheet1?

Unnecessary Worksheet Formula References In Excel

If you have ever written an Excel formula where you are referencing multiple worksheets you may have experienced this annoyance. It occurs when you begin writing a formula in one sheet, click onto another sheet, and then navigate back to the original sheet. These "Off-Sheet" references are written automatically and most people just let it stay in there and don't bother to manually go back and clean up the formula. Hey, if it works why create more work?

EXCEPT!!! If you are extremely anal (I'm super guilty of this), then you painstakingly go through the formula (no matter how long it is) and delete all those same-sheet references. I not only like doing this to satisfy my need for order, but it also makes the formula a heck of a lot easier to digest when you are trying to figure out what it actually does three months from now!

Now that I have described the manual way to clean up a formula, let's look at a split-second way to automate this task. I give you the "RemoveSameSheetReferences" macro! Enjoy :)

Sub RemoveSameSheetReferences()
'PURPOSE: Removes Sheet References from formulas when not needed

Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long

Set sht = ActiveSheet

fndList = Array("'" & sht.Name & "'!", sht.Name & "!")
rplc = ""

'Optimize Code
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual

'Loop through each item in Array lists
  For x = LBound(fndList) To UBound(fndList)
    sht.Cells.Replace What:=fndList(x), Replacement:=rplc, _
      LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
      SearchFormat:=False, ReplaceFormat:=False
  Next x

'Optimize Code
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic

End Sub

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)