Dependent dropdown menu create & refresh

I'd like to have a code that creates and updates a dependent dropdown menu.
The source for the dropdown is from Table1 in Sheet1. This table has two columns. One is called Category and the other Sub-category.
Creating the dependent dropdown should be iterative in the sense that for every category it automatically creates the table structure and names (names in the name manager) necessary to create the dependent dropdown menu. As the categories and sub-categories will change, and therewith thus names in the name manager might become redundant or duplicated my thought is to create a sheet that keeps track of all names created so those can be removed and created again after each execution of the code (there will be more names in the workbook and therefore there should also be a check if the name already exists and use another one).
The dropdown should be in sheet 2. The first in G2 and second in J2.

I don't know what is easiest, but I thought that the code can remove the dropdown and then add it again after each run. Just so to ensure the data validation rules are ok.
And if there are no dropdown menu's that it creates them.

For this I created a code and that supposes to already does some of these things.
However, I am getting errors and don’t know how to do all of it.

What I have is:

Please Login or Register  to view this content.

What I am trying to resolve is:
1) The error I get is: ‘Run time error 1004: Application-defined or object-defined error. This in the following line of code:

Please Login or Register  to view this content.

.
2) It doesn't create the names in the name manager, something goes wrong here. In an old version (see attached Excel CreateDependentDropdownOld) of the code it does. I don’t understand the difference why in one it does create the names in the name manager whilst in the version I updated, to always use the same sheetname for saving the names, it does not work anymore.
3) I was unable to make the code such that I am sure that it only deletes names in the name manager that exist in the sheet “DropdownNames”.

Note: Now it creates some sort of library of all names it generated in the name manager. Meaning that it doesn’t remove them. I’ll try to integrate this myself once the rest of the code works – should not be that difficult.

I hope you can help out. I attached a sample Excel.
And also please let me know that if I am over complicating things and there is a far simpler way to do this.

Thank you!

Since you're using Excel 365, please check out this tutorial from Mynda Treacy:
Easy Dynamic Dependent Data Validation Two Ways
https://www.youtube.com/watch?v=pjLAnpBM9dk&t=192s

Thank you!
Very elegant. I am almost embarrassed that I didn't find it on Youtube.
This works perfrectly on my pc.

Is there also a way to get this to work on office 2019? A user of me is using this.

Originally Posted by M_M_Mischa

Is there also a way to get this to work on office 2019? A user of me is using this.

There are plenty of examples in this forum on how to do it by formula, but I believe it involves creating multiple named ranges, which makes it difficult to maintain if you add more categories to your data. However, I've written a macro to simplify setting up multiple dependent data validations. It works in Excel 2007 or newer. Let me know if you're interested.