Macro to Adjust Existing Range Names Source

[Avaran]

Good morning.

I've inherited a very complex set of 4 spreadsheets at work this week. It's running extremely slow and I am trying to fix a lot of issues with it. One thing that is concerning me is the workbooks are filled with hundreds of range names that reference the entire column of each column that it stands for. So Column A is a range name that is simply A:A.

The sumifs looking at these are then looking at the entire column. Can anyone help me out with a macro that will go through each range name in a worksheet and shorten it's length to whatever the last row of data is?

Thanks for your help, I really appreciate this!

 

[RoryA]

SUMIFs is actually pretty efficient in that it only works on the used range anyway. Are you sure that's what's slowing it down?

 

[Avaran]

Sadly, no. The files have thousands of SUMIFs in them. I'm working on stripping out the unused and unnecessary ones right now. If the range names aren't causing them to SUMIF the entire column then it shouldn't be that. Right now, the files are taking in excess of 10 minutes to close out. There's 4 files with sumifs reaching back and forth between the files.