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
..

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

" wrote:

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
..

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!

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



wrote in message
oups.com...
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!

Thanks Tom! I will try it

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!