Finding the most occurrences of a value in a data set, using VBA.

[Technology]

With this data starting at A1, how would we go about finding which column has the most occurrences of 0's. Using VBA function, place the result in A25.

7	3	8	6	3	6
1	6	9	4	2	5
5	10	10	4	6	2
1	7	6	7	0	6
1	5	6	3	9	7
1	7	8	8	1	6
2	8	0	6	0	2
7	5	10	1	3	0
8	5	9	9	4	0
6	4	3	2	4	2
8	4	7	6	6	9
6	4	1	4	3	1
2	3	0	8	8	4
0	3	5	4	7	6
7	1	0	0	8	3
4	1	8	3	9	6
4	1	1	10	3	5
7	3	0	4	1	4
3	6	6	9	6	1
8	2	3	6	6	8
0	0	1	10	1	2
5	2	9	4	3	7
5	7	3	8	1	6

 

[Zot]

Thank you, I would prefer that the code stated that just C based on the data had the most zeros compared to the other columns. Maybe having a count of each column then the code selecting the highest value. I'm not sure the best way of solving this one but I just want to say that I really appreciate your help. I'm definitely learning.

I did not get why you were saying I would prefer that the code stated that just C based on the data had the most zeros compared to the other columns when A and F have same count of zeros. Any other specific criteria?

 

[Peter_SSs]

I would prefer that the code stated that just C based on the data had the most zeros compared to the other columns.

It does!
Here is your original sample data after the code has been run.
Is there anything about this result that does not match your requirement?

Technology.xlsm
	A	B	C	D	E	F
1	7	3	8	6	3	6
2	1	6	9	4	2	5
3	5	10	10	4	6	2
4	1	7	6	7	0	6
5	1	5	6	3	9	7
6	1	7	8	8	1	6
7	2	8	0	6	0	2
8	7	5	10	1	3	0
9	8	5	9	9	4	0
10	6	4	3	2	4	2
11	8	4	7	6	6	9
12	6	4	1	4	3	1
13	2	3	0	8	8	4
14	0	3	5	4	7	6
15	7	1	0	0	8	3
16	4	1	8	3	9	6
17	4	1	1	10	3	5
18	7	3	0	4	1	4
19	3	6	6	9	6	1
20	8	2	3	6	6	8
21	0	0	1	10	1	2
22	5	2	9	4	3	7
23	5	7	3	8	1	6
24
25	C
Sheet2

 

[Marc L]

As a starter :​

Sub Demo1()
          Dim N&(), V, C%, M&
    With [A1].CurrentRegion.Columns
        ReDim N(1 To .Count)
            V = Evaluate("IF({1},CHAR(64+COLUMN(" & .Rows(1).Address & ")))")
        For C = 1 To .Count:  N(C) = Application.CountIf(.Item(C), 0):  Next
            M = Application.Max(N)
        For C = 1 To .Count
            If N(C) < M Then V(C) = False
        Next
            V = Filter(V, False, False)
            If UBound(V) > -1 Then Cells(.Rows.Count + 2, 1).Resize(, UBound(V) + 1).Value2 = V
    End With
End Sub

 

[Technology]

It does!
Here is your original sample data after the code has been run.
Is there anything about this result that does not match your requirement?

Technology.xlsm
	A	B	C	D	E	F
1	7	3	8	6	3	6
2	1	6	9	4	2	5
3	5	10	10	4	6	2
4	1	7	6	7	0	6
5	1	5	6	3	9	7
6	1	7	8	8	1	6
7	2	8	0	6	0	2
8	7	5	10	1	3	0
9	8	5	9	9	4	0
10	6	4	3	2	4	2
11	8	4	7	6	6	9
12	6	4	1	4	3	1
13	2	3	0	8	8	4
14	0	3	5	4	7	6
15	7	1	0	0	8	3
16	4	1	8	3	9	6
17	4	1	1	10	3	5
18	7	3	0	4	1	4
19	3	6	6	9	6	1
20	8	2	3	6	6	8
21	0	0	1	10	1	2
22	5	2	9	4	3	7
23	5	7	3	8	1	6
24
25	C
Sheet2

Thank you! @Peter_SSs

That's correct! I am unsure of how to obtain the code for that, though. I don't seem to have the ability to add-in L2BB.

 

[Peter_SSs]

That's correct! I am unsure of how to obtain the code for that, though.

The code is in post #7

 

[Marc L]

​

Like in post #13 !​

​

Solution belongs to good reader …​