Count Unique accross mulitple columns

[Rob Drummond, Jr]

I have tried everything I can think to do and I can not seem to get the
results I want. What I have is a spreadsheet with several columns and I want
to have excel count the unique values across multiple columns. The columns I
want counted are part number columns that are separated by cost columns. I
can get excel to show the unique part numbers for a given column, but it is
possible for the same part number to be in more than one column. Here is the
basic layout....

part# cost part# cost part# cost
123 456 789
456 789
789
123 789

I can get excel to look at column 1 (or 3 or 5) and tell me each unique part
number.

Column 1
123
456
789

What I want is for it to look across column 1, 3, and 5 and give me a single
list of all unique part numbers instead of a list for each of the columns.
In other words, I want the list to be like the above instead of...

Column1 Column 2 Column 3
123 456 789
456 789
789

The end result will be, once the list is created, to have excel count how
many times total each part number appears in the three columns (I can do that
part). If this question makes no sense, that is probably the reason I can't
do what I want.

 

[Rob Drummond, Jr]

Once I got this macro edited (copy and paste put a few <enter> and odd things
that excel didn't like) so it would run, I got "Unable to get CountIf proerty
of the worksheet function class" error on the line...

If Application.WorksheetFunction.CountIf(ResultRng, Cel.Value) = 0 Then

any guess what I did wrong? I wish I was better with macros. My work with
Access has helped but I am still very weak when it comes to macros.

 

[Rob Drummond, Jr]

When I tried the copy and paste from your original post, it had several lines
in red and when I ran the macro, each of those lines caused an error. I will
try it again and see if I can get it to work. Am I correct in thinking I
should be copying from "Sub" to "Sub End"?

 

[Rob Drummond, Jr]

One last thing (I promise), it seems my result range has to equal the total
number of cells in the inquiry. For example, if each column has 20 cells and
there are three columns, I need a results range of 60 cells, even though only
7 unique values are found/counted. Am I doing something wrong or is this
just an inherent requirement of the macro?