-1

I am running into a problem in VBA in excel. I am trying to create a participant registration program in excel for a sports tournament. One can add data like the weight, age and name of a participant. And then based on that, The participants are divided into poules named with letters A, B... until Z. I have a table which can sort these poules by letters and then for example only display the participants which are in poule A such as below

Example

Now i want to count the numbers with VBA macros of participants in the poule which is displayed by the sorted table. For example when i sort on poule A it has to return 3 and when i sort on B, it has to return 2.

Determined by the number of participants in a poule the program will print a different poule scheme, depending on the number of participants. (i have this part in VBA)

The problem is when i have a sorted table like below

Example 2

It counts all the participants, and not just the ones in B or any other sorted letter.

I have this: Sub Count()

Dim nRows As Integer

nRows = Range(Range("A18"), Range("A18").End(xlDown)).Rows.Count
MsgBox (nRows)

End Sub

This works well if you sort A, but when you sort any other letter, it counts All the table until that letter. Eg. when you sort B, it displays 5 (Number of A + B).

I have looked on the internet for a really long time to find a solution but without succes.

I hope my question is clear and that somebody can help me.

Denn159
  • 1
  • 2

3 Answers3

0

I am assuming that you are using worksheet functions. Use Subtotal when working with filtered data.

These parameters evaluate all 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 only visible cells

  • 101 AVERAGE
  • 102 COUNT
  • 103 COUNTA
  • 104 MAX
  • 105 MIN
  • 106 PRODUCT
  • 107 STDEV
  • 108 STDEVP
  • 109 SUM
  • 110 VAR
  • 111 VARP
0

Range.SpecialCells will return a range of only visible cells.

Dim rSource As Range
Dim rVisibleCells

Set rSource = Range(Range("A2"), Range("A2").End(xlDown))
Set rVisibleCells = rSource.SpecialCells(xlCellTypeVisible)

MsgBox rVisibleCells.Rows.Count
  • Thank your for your answer, – Denn159 Jun 28 '16 at 16:35
  • The code does work now, except that it only counts the first letters it encounters. So when the first column for the poules is for example A A A E A A B B E. And i sort to A and use the count function, it only returns a value of 3 and not of 5 (because there are 5 A's) When I sort the table to A, it looks like this (column number, poule value): 14 A 15 A 16 A 18 A 19 A And it returns just 3, have you maybe got any fixes for that problem as well? – Denn159 Jun 28 '16 at 16:36
  • Do you want to filter the list? Or do you want a filtered array? –  Jun 28 '16 at 16:42
  • I want to filter the list, and then return a count of the size of the poules, i added a comment to my question with a pic of the problem – Denn159 Jun 28 '16 at 16:45
  • I don't understand you? – Denn159 Jun 28 '16 at 16:54
  • The range was starting at A18. I fixed it to start at A2. Let me know if this fixes it. –  Jun 28 '16 at 18:38
  • Glad to help. Sorry I missed it to begin with. –  Jun 29 '16 at 10:22
0

The code does work now, except that it only counts the first letters it encounters. So when the first column for the poules is for example A A A E A A B B E. And i sort to A and use the count function, it only returns a value of 3 and not of 5 (because there are 5 A's)

When I sort the table to A, it looks like this (column number, poule value):

14 A

15 A

16 A

18 A

19 A

And it returns just a count of 3, have you maybe got any fixes for that problem as well?

Pictures:

sorted tabel to E

Table

Denn159
  • 1
  • 2