Remove Unnecessary Sheet References From Excel Formulas With VBA Macro
What This VBA Code Does
Have you ever seen a formula in Sheet1 where it is written with references to Sheet1?
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
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
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 SubUsing VBA Code Found On The Internet
Now that you’ve found some VBA code that could potentially solve your Excel automation problem, what do you do with it? If you don’t necessarily want to learn how to code VBA and are just looking for the fastest way to implement this code into your spreadsheet, I wrote an article (with video) that explains how to get the VBA code you’ve found running on your spreadsheet.
Getting Started Automating Excel
Are you new to VBA and not sure where to begin? Check out my quickstart guide to learning VBA. This article won’t overwhelm you with fancy coding jargon, as it provides you with a simplistic and straightforward approach to the basic things I wish I knew when trying to teach myself how to automate tasks in Excel with VBA Macros.
Also, if you haven’t checked out Excel’s latest automation feature called Power Query, I have put together a beginner’s guide for automating with Excel’s Power Query feature as well! This little-known built-in Excel feature allows you to merge and clean data automatically with little to no coding!
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 get 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 that 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!
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.