my problem:
i have a simple sheet with a list of names and some other data, what i
would like to do is count the occurance of each unique name and then in
another sheet print the name and the number of times it occurs. i want
to be able to open the sheet, and run the macro with no user involvment.
sheet1 sheet2
abe abe 4
abe bill 2
abe
abe
bill
bill

my first thought was to use the autofilter method. i thought of this
because if i do it manully excel allows me to choose from the drop down
menu what i would like to filter the sheet by and it lists each unique
name. after exploring the autofilter, filters, and filter help vba notes
i can not find a way to extract the criteria from that 'magical' excel
list. is this possible and if so how would you go about that? the names
are unknown and can change from day to day, so i can't keep a list of
names and use it as criteria 1 if i were to call the autofilter method.

my second though was to create a dynamic array and read each name from
the sheet and add it to the array only if it is unique. then apply the
autofilter method with array as criteria1 and then count the result:

'the first name is of course going to be unique
totalnames = 1
x = 1
uniquename = true
names(1) = cell(1,1).value
rowctr = 2
while not end of sheet
for x = 1 to totalnames
if names(x) = cell(rowctr,1).value
uniquename = false
x = totalnames + 1 ' end loop
else
uniquename = true
end if
next x
if uniquename
totalnames++
names(totalnames) = (rowctr,1).value
end if
rowctr++
end loop

assuming the above logic is correct (not the syntax) i would have an
array with the unique names.

can i set the criteria1 field for autofilter using a variable?
range.autofilter field:= 1 criteria1:=names(x)? if so from that point i
can just count the visible rows (usedranged.rows.count)?
or add a new column with the number 1 beside each name and use the sumif
function? sumif(range, names(x), range2)?

i'm pretty sure that the second method would work, but i'm new to vba
and am trying to learn new ways of manipulating the data. it seems to be
that i can skip a lot if i can get the criteria the excel displays in
that drop down menu.

any ideas or suggestions for this project is appreciated. it will be a
great time saver.

TIA

bst