I think you might be able to set up a dictionary object to hold the last used cell for every SheetName/NamedRange combination.
If you set up a workbook with named ranges etc. in and attach it here along with a text file, I'll give it a go.