Number of different items in an entire column without knowing if some of the cells are blank

[sam.fares]

Hi all:

i posted similar question before but i would like to expand on it.

i have marks(J1,J2,J3,.....) that might repeat in column A, but i would
not know the extent of how many cells in the entire column (65536)cells
will have marks in them. on some projects i might have every cell of
that column has a mark in it,and on some other projects i might have
marks in 10000 cells of that column. it depends on the project.

is there a macro that will tell me how many different marks in that
column, becuse that number will determine the number of my loops.


the way the marks are in column A is

J1
J1
J1
..
..
..
J2
J2
J2
J2
..
..
J3
J3
..

 

[Guest]

Sam,

You can count the total number of rows being used with the UsedRange method.
Something like the following will tell you the max number of rows being used

lngMaxRows = activesheet.usedrange.rows.count

http://HelpExcel.com

 

[sam.fares]

Thanks!

i have been able to determine the number of rows in column A that has
marks like J1,J2,.... with this code line:

LastRow = shtQDS.Range("A3").End(xlDown).Row

but i dont know how to write the syntax to tell me how many different
marks between cell A3, columnA and row"LasrRow".

I would appreciate any help with this!

 

[Tom Ogilvy]

that would indicate no blank cells, so

Sub AA()
Dim lastrow As Long
Dim numUnique as Long
lastrow = Range("A3").End(xlDown).Row
numUnique = Evaluate("Sumproduct(1/countif(A3:A" & lastrow & _
",A3:A" & lastrow & "))")
MsgBox "Number of uniques: " & numUnique
End Sub

Regards,
Tom Ogilvy

 

[sam.fares]

Thanks Tom! I will try it

 

[sam.fares]

Tom:

when i tried it , it gave a run-time error 13, type mismatch after
trying to move on from the line where "numUnique" is and the next line.

what does seem to be the error? Thanks!

 

[Tom Ogilvy]

the code was copied directly from a module where it works very well.

Do this, got to a blank sheet on that page on put in this formula

=SUMPRODUCT(1/COUNTIF(A3:A11,A3:A11))

change the 11 to reflect the last row of your data.

That is what the code is doing, but without entering it into a worksheet.

What version of Excel? I will test it in Excel 97 tonight and see if it has
a problem with over 10000 cells in a range.

If so, I will come back with a dick and jane solution if someone has't
already done it. You said you data is sorted on column A, correct?

 

[Tom Ogilvy]

It worked for 43,000 cells in xl97 but it took about 3 minutes.

this will be much faster for a large number or rows:
Sub AB()
Dim nodupes As New Collection
Dim lastrow As Long
Dim numUnique As Long
Dim v As Variant, i as Long
lastrow = Range("A3").End(xlDown).Row
Set rng = Range("A3:A" & lastrow)
v = rng
On Error Resume Next
For i = LBound(v) To UBound(v)
nodupes.Add v(i, 1), CStr(v(i, 1))
Next
On Error GoTo 0
numUnique = nodupes.Count
msgbox "Number of Uniques: " & numUnique
End Sub

 

[sam.fares]

Tom:

I appreciate your help very much. it is working great!

 

[sam.fares]

Tom:

one more question, and apprecaite an answer, what is the following
syntax in your last code you sent me do?

nodupes.Add v(i, 1), CStr(v(i, 1))

 

[Tom Ogilvy]

it assigns the value in each cell to the collection and indexes it on the
value. If indexes are used (as I have) a collection doesn't allow duplicate
index values - so it raises and error rather than assign the value. The on
Error ignores the error and continues on. The end result is that the
collection nodupes holds a list of the unique entries in your range.

so you could also look at no dupes and see what the unique entries are as
shown in the code at the bottom:

Sub AB()
Dim nodupes As New Collection
Dim lastrow As Long
Dim numUnique As Long
Dim v As Variant, i As Long
Dim sStr As String
Dim itm As Variant
lastrow = Range("A3").End(xlDown).Row
Set rng = Range("A3:A" & lastrow)
v = rng
On Error Resume Next
For i = LBound(v) To UBound(v)
nodupes.Add v(i, 1), CStr(v(i, 1))
Next
On Error GoTo 0
numUnique = nodupes.Count
MsgBox "Number of Uniques: " & numUnique
sStr = ""
For Each itm In nodupes
sStr = sStr & itm & vbNewLine
Next
MsgBox sStr


End Sub