oteixeira62
You asked: Do you know a solution for this not using vba?
I can't offer a solution without first knowing what it is your macro is designed to achieve.
This is what I wrote in my first post:
So would you be willing to back up and describe in normal vocabulary (i.e., not in Excel language) what it is that the data validation list contains, what you want to extract from it ... etc. What is the purpose being served?
Said another way, once we're clear on the end desired (the purpose being served), it's entirely possible that there is another cleaner way to get there. You've assumed that a macro is the way and are just looking to refine that method. I'm suggesting there may be a different way altogether.
If such solution existed do you think I will spend my time re-inventing the wheel...
Not if you knew what that solution is; of course not. But I definitely have known that to happen, and more than once, often with people who are adept at programming. They just assume that a VBA would work, they understand how it can work, how it can be written, and so will write a routine that works and are happy with it. As I say, especially people with a background in programming. Here's a true story that illustrates the point:
Years ago (decades ago, actually), I had responsibility for creating a monthly headcount report for a major US corporation. I was the director of the HR/Payroll database for the company, and that monthly report was part of my group's responsibility. What I learned was that the people in the IT department (the group that supported us) had written a very elaborate program to extract data from the (mainframe) database each month and break it down by all the necessary categories. It took something like 30 minutes for that program to run. It did work; the report created was reliable. But my sense was that it could be done more quickly just in a native spreadsheet way. I created an SQL query to extract the data necessary and export it to (at the time) Lotus 1-2-3. And then used native spreadsheet functions to produce the report in minutes, start to finish. The IT folks had the tool they were comfortable with and used it; so never bothered to investigate what the spreadsheet could do. Does that happen still? Of course.
I don't by any means know that this is your situation. Nor am I accusing you of doing that. All I said at the very start was that your description (although I'd like to have you describe it more fully) made it sound like something that native Excel functions might handle relatively easily. But it only sounded like it; it wasn't clear to me what the VBA routine is doing, and without that clarity, I don't know for sure.