Modifying named range fails inside macro but succeeds when continued

I got a macro that is gathering rows within named range:

Please Login or Register  to view this content.

sh is a Worksheet and r is a Range and NamedRangeExists is

Please Login or Register  to view this content.

I do not know why but it stopped running smoothly after some time/modifications to the code. Now when second row is added to the range (bold line) it fails with error 1004 ("formula contains an error" however all addresses returned are fine). What is strange is that it runs fine when I force to continue the macro. Any idea what is going on?

hello,

I change a bit try below code, I hope it will be work for you

Please Login or Register  to view this content.

Your code works when I try it or even slightly modify to my situation (where range consists of entire rows and some of them are added to the named range in a loop) but my code still produces this strange error (which disappears when rerun/continued in break mode).
I could send you the file I have but my office version is 2010 so I am not sure you will be able to repeat it?

Hi krzcho
This would be your best bet

I could send you the file

=$1:$1 did the trick but error is still there...
manually making address instead of using Union does not help...
Office looks patched...
Finally I have found it!
http://social.msdn.microsoft.com/For...1-c17d957cbb5c
http://www.jkp-ads.com/Articles/ExcelNames10.asp
One day behind

Question is: whether it is me who does not understand RefersTo requirements on is it a bug in Excel?
http://msdn.microsoft.com/en-us/library/ff835300.aspx
defines RefersTo as "Describes what the name refers to, in English, using A1-style notation"; does English defines list delimeter? I always though that macros work with "," separator only while my code now works only if i replace address delimiter from "," to my local one (";")?

It looks like it worked in break mode as excel was switching from system language to plain english. Weird.

Please Login or Register  to view this content.