VBA code to count unique words in a range of cells

Hello, I am trying to find a way to count the number of unique words in a range of cells when each cell contains numerous words. For example:

The dog and the cat are asleep.
The cat is inside.
The dog is outside.

Each of the above sentences would be in their own cell. I'm looking for a way to count the unique words in the above range, which should be 9 for "The, dog, and, cat, are, asleep, is, inside, outside". I've found formulas to count unique cells, but not unique words. Ideally, I want to put this into VBA so that all I would need to do is highlight a range and run the macro.

Is this possible? Can anyone offer some assistance?

Thanks.

Try

Please Login or Register  to view this content.

Duplicate........

@Jindon, your code comes close, but it is counting how many times each unique word appears and printing out all of the unique words. Like so:
FirstTry.jpg

That would give a total of 15. I don't need to know how many times each word appears. Is there a way to weed that out? I could do without it printing all of the unique words as well, but that's not necessary.

Originally Posted by jindon

Try

Please Login or Register  to view this content.

@XOR LX, I could work with something purely formula-based.

Please Login or Register  to view this content.

@XOR LX, Thanks, but jindon's solution will be much easier for me to share with some of the other's who are not Excel savvy at all. I appreciate the effort.

@jindon, I know this would be a bit out of scope, but do you know a way to set this so it grabs the values from all column A's in the workbook?

Originally Posted by jindon

Please Login or Register  to view this content.

scottyblaze

Before anything, please edit your post #9.
VBA code should wrapped with code tags.

i.e
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

and full quotes should be added only if it is really necessary.

OK thanks for the edit

Please Login or Register  to view this content.

Sorry about that. I'm a bit new here. I'll try to be more mindful.

Did you try the code in my previous post?

I was just able to try it out. It looks like a winner! I can't thank you enough.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.