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 ...
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.
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.