Forum Discussion
Named Ranges with _xlfn. prefixes
Hello, I have some VBA code to makes a list of Named Ranges in a workbook. It then visits each of these named ranges and grabs some data from them for further processing. Today, I had this code ...
Hello All,
Sorry for the late update, but I got to the bottom of what was going on with the Named Ranges with names starting with _xlfn. To solve the problem, I filtered so that the code will skip these ranges. But it only took a coworker to choose one of these functions, to make these named appear. He did not even use the function, merely was checking to see what was out there. I was at his machine the other day, and in the list of recently used functions, there it was!!! Wow. Thanks for all your input I used some of them and learned from the others. Great Job All!
Sorry for the late update, but I got to the bottom of what was going on with the Named Ranges with names starting with _xlfn. To solve the problem, I filtered so that the code will skip these ranges. But it only took a coworker to choose one of these functions, to make these named appear. He did not even use the function, merely was checking to see what was out there. I was at his machine the other day, and in the list of recently used functions, there it was!!! Wow. Thanks for all your input I used some of them and learned from the others. Great Job All!
You could run this code, which will make all names visible, after which you can delete them from the name manager. See https://www.mrexcel.com/board/threads/cant-swap-reference-style-between-r1c1-and-a1-due-to-_xlpm.1191055/
VBA Code:Sub CleanNames() Dim Nm As Name For Each Nm In ActiveWorkbook.Names Nm.Visible = True Next Nm End Sub- LittleHelper that is a great tip BUT be careful you are 'breaking' a functionality excel has built in to prevent problems when opening in older sheets (and maybe other cases?). Also I was doing some testing and it appears that once you delete it, it doesn't seem to come back. So I had parameter list:
abc, abcd, abcde, abcdef, abcdefg
I started with a LET statement with abcdefg and then editted it and used the backspace key delete the letters one at a time creating all those intermediate parameters until I ended with abc.
so I used this trick and deleted all except abc
then I edited the LET statement again and made it abcdef and neither that final parameter nor the intermediates were re-created.
so although you can 'CLEAN' up those variables I would be very careful as there is still more going on (or not going on) that we don't know.
