Excel Named Range Corruption

[Martel]

My excel workbook has lots of VBA code and makes extensive use of named ranges. No errors reported when opening or saving file. VBA code compiles without error. 'Refers to' box in named range definition repeatedly becomes corrupt with #REF error. I have repeatedly corrected the error, resaved and reopened file but error immediately comes back. Very frustrated. Does anybody know how to cure this problem? Thanks

 

[davesexcel]

Show the codes, something must be getting deleted

 

[Martel]

My thanks to davesexcel and any others. I have discovered the hard way, by doing it the "wrong" way, how to destroy a dynamic named range. Scenario: Master sheet with people, male, female, phone numbers etc. Wanted sub sheet with Males only. Copied all data to sub sheet for Males only and set up various dynamic named ranges. Then discovered not all records had phone number. So filter out any on sub sheet with blank phone number and deleted those rows, then in VBA did 'showalldata'. Result was as intended - now showing only Male records on sub sheet with phone number. However, this destroys the identity of the dynamic range. Strange because dynamic ranges are supposed to maintain their integrity even if you add/delete rows. But in the above situation, Excel does not like it - produces #REF error and crashes the project. Solution was obvious - filter out rows with blank phone number, as well as gender, before copying to sub sheet. I did not expect Excel to behave like that. Learn something new everyday. So daveexcel you were correct but not for reasons I anticipated. Thanks all