Excel Named Range Corruption

Martel

New Member
Joined
Jun 19, 2019
Messages
23
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Martel

New Member
Joined
Jun 19, 2019
Messages
23
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,528
Messages
5,636,852
Members
416,945
Latest member
Himu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top