Need ADO alternative VBA to merge and remove duplicates of multiple Named Ranges

Hi Gurus,

I need guidance or the following requirement.

I have multiple of named ranges that are to be merged, de-duplicated and updated back to all of those ranges again. Example: I am adding the following screenshots to understand the before and after running the macro.
Before:
Attachment 546242

After:
Attachment 546243

Basically, it is merging, de-duplicating and updating back to the same named ranges. As list of columns need to be compared is huge and in various combination, I thought using ADO solves my problem quickly. SQL Union results in mergin and de-duplication automatically. I am able to achieve this very quickly. Only problem is that, those columns outside the first 256 columns of spreadsheet are resulting in error. Please find the attached sample file with ADO solution.

ADO On Excel - Sample file.xlsm

Is there a way to overcome this issue with ADO or any other suggestions to deal this with VBA code?

Thanks for all your help.

Power Query will do, but you got to have excel 2016 which has Power Query built-in it. If you have excel 2013, you still can use Power Query, but it is not part of excel, so you have to download it as add-ins.
Power Query has all the union, merge, append and join functions.

Hi AB33,

This is more of a VBA problem. There is a huge list of Named ranges which has a certain logic to loop through and comparing between related lists. The goal is to make sure the lists are up-to-date. The only complexity is to compare between multiple lists. Could you suggest VBA alternative using Arrays to merge and de-duplicate between multiple lists and updating them back to the named ranges compared?

Thank you.

Well, What ever you could using ADO could now be done using Power Query, but without writing any code. Access has a 256 columns limits, that is the reason for getting an error once you are over the limit. If your data is in excess of 256 columns, no amount of array would be an ideal solution. I suspect you are using a wrong tool and should look at Power Query.

Hi AB33,

I think you got the wrong perspective of my problem. I am not trying to merge 256 columns. I am trying to merge only 2-4 columns at a time. Though the problem is that there are more than 500 columns which needs to be tackled in a loop. What columns needs to be merged and updated, will be decided a settings sheet which has the list of all Named Ranges from Sheet1. This specific logic to loop is already in place.

Power query is a powerful tool with nice features, but still I need to iterate through all the 200 iterations of comparisons manually which is not a usable solution. Even if there is a way to automate using Power Query, I am stuck with MS Office 2010 version.

That is the reason, I am looking for some solution using VBA/Arrays to tackle these individual iterations of Merging/De-duplication/Updating the columns. I am not very proficient using Arrays, so sought help from the forum.

Hope this clarifies my position.

Thank you.