Excel VBA selecting data from a sorted Table -

i running problem in vba in excel. trying create participant registration program in excel sports tournament. 1 can add data weight, age , name of participant. , based on that, participants divided poules named letters a, b... until z. have table can sort these poules letters , example display participants in poule such below

example

now want count numbers vba macros of participants in poule displayed sorted table. example when sort on poule has return 3 , when sort on b, has return 2.

determined number of participants in poule program print different poule scheme, depending on number of participants. (i have part in vba)

the problem when have sorted table below

example 2

it counts participants, , not ones in b or other sorted letter.

i have this: sub count()

dim nrows integer  nrows = range(range("a18"), range("a18").end(xldown)).rows.count msgbox (nrows)  end sub 

this works if sort a, when sort other letter, counts table until letter. eg. when sort b, displays 5 (number of + b).

i have looked on internet long time find solution without succes.

i hope question clear , can me.

i assuming using worksheet functions. use subtotal when working filtered data.

these parameters evaluate cells visible or hidden

• 1 average
• 2 count
• 3 counta
• 4 max
• 5 min
• 6 product
• 7 stdev
• 8 stdevp
• 9 sum
• 10 var
• 11 varp

these parameters evaluate visible cells

• 101 average
• 102 count
• 103 counta
• 104 max
• 105 min
• 106 product
• 107 stdev
• 108 stdevp
• 109 sum
• 110 var
• 111 varp