Using Excel 2000
Hi,
I was using the macro below with only 1000 - 1500 rows, but now I want to use and check played with 59000 rows extending the range it is taking 45-55 minutes to update data results.
My enquiry is it any other formulas or VBA code which can reduce the time for update data results.
In the column "A" there is entered year.
In the column "B" there is entered results.
In the column "C" there is Check Played.
Row 3 has header.... Year, Results, check Played, and year to check 00/01 to 19/20
When I run the macro below it takes 45 - 55 minutes
Thank you in advance
Regards,
Kishan
Hi,
I was using the macro below with only 1000 - 1500 rows, but now I want to use and check played with 59000 rows extending the range it is taking 45-55 minutes to update data results.
My enquiry is it any other formulas or VBA code which can reduce the time for update data results.
In the column "A" there is entered year.
In the column "B" there is entered results.
In the column "C" there is Check Played.
Row 3 has header.... Year, Results, check Played, and year to check 00/01 to 19/20
When I run the macro below it takes 45 - 55 minutes
* | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y |
1 | |||||||||||||||||||||||||
2 | |||||||||||||||||||||||||
3 | Year | Results | Check Played | 00/01 | 01/02 | 02/03 | 03/04 | 04/05 | 05/06 | 06/07 | 07/08 | 08/09 | 09/10 | 10/11 | 11/12 | 12/13 | 13/14 | 14/15 | 15/16 | 16/17 | 17/18 | 18/19 | 19/20 | ||
4 | 00/01 | X | X | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
5 | 00/01 | X | 1 | 1 | 2 | 1 | 2 | X | 1 | 1 | 2 | X | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | ||
6 | 01/02 | X | X | X | X | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
7 | 01/02 | 1 | 1 | 1 | 1 | X | X | 1 | 2 | 1 | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | ||
8 | 01/02 | X | 1 | 1 | X | 1 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
9 | 01/02 | 1 | 1 | 1 | 1 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
10 | 02/03 | 1 | 1 | 1 | 2 | X | 1 | X | 1 | X | 1 | 1 | X | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | ||
11 | 02/03 | 1 | X | 2 | 1 | 1 | X | 1 | X | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
12 | 02/03 | 1 | X | 1 | X | 1 | 1 | 1 | 2 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
13 | 02/03 | X | X | X | 1 | 1 | X | X | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
14 | 02/03 | X | X | 2 | 2 | 1 | X | X | X | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
15 | 03/04 | 1 | 1 | 2 | 1 | 1 | X | X | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
16 | 03/04 | X | 1 | 1 | 1 | 1 | X | 2 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
17 | 03/04 | X | 2 | X | 1 | 1 | 1 | 1 | X | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
18 | 03/04 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | X | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
19 | 03/04 | 1 | 2 | 1 | 2 | X | X | X | X | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
20 | 06/07 | 1 | X | X | 1 | 1 | 1 | 1 | X | X | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
21 | 06/07 | 1 | X | X | 1 | 1 | 1 | 1 | X | X | X | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
22 | 06/07 | 1 | 1 | 1 | X | 1 | 1 | 1 | X | X | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
23 | 06/07 | X | 1 | 2 | X | 1 | 1 | 1 | X | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
24 | 06/07 | 1 | 1 | 1 | X | 1 | 2 | 1 | X | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
25 | 08/09 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
26 | 08/09 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
27 | 08/09 | 2 | 1 | X | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
28 | 08/09 | 1 | 1 | 1 | 1 | X | 1 | 1 | 1 | 1 | X | 1 | X | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
29 | 08/09 | X | 1 | 2 | 1 | 2 | X | 1 | X | 1 | 1 | X | X | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | ||
30 | 08/09 | 1 | X | 2 | 2 | 2 | 1 | 1 | 1 | 1 | X | X | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
31 | 10/11 | 1 | X | 1 | 1 | X | X | 1 | 1 | 1 | X | X | X | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
32 | 10/11 | 1 | X | 1 | 2 | 2 | 1 | 1 | 1 | 1 | X | X | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
33 | 10/11 | X | 1 | X | 1 | X | 1 | X | 1 | 2 | 1 | 2 | X | X | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | ||
34 | 10/11 | X | X | X | 1 | 1 | 1 | 1 | 1 | 1 | X | 2 | X | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
35 | 10/11 | 1 | 1 | 1 | 1 | 2 | 1 | X | 1 | X | 1 | 2 | 1 | 2 | X | X | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | ||
36 | 10/11 | 2 | X | 2 | X | 1 | 2 | 1 | X | X | 1 | 2 | 1 | 2 | X | X | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | ||
37 | 10/11 | 2 | 1 | X | 1 | 1 | 1 | 1 | 1 | X | 1 | 2 | 1 | 2 | X | X | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | ||
38 | 11/12 | 1 | 1 | 1 | 1 | X | 1 | X | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 2 | X | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
39 | 13/14 | X | 1 | 2 | 1 | 2 | X | X | 1 | X | 1 | 2 | 1 | 2 | X | X | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | ||
40 | 13/14 | 1 | X | 2 | 2 | 2 | 1 | X | X | 1 | 1 | 1 | 1 | 2 | X | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
41 | 14/15 | 1 | X | 1 | 1 | X | X | 2 | 2 | 1 | 1 | 1 | 1 | 2 | X | 1 | X | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
42 | 14/15 | 1 | X | 1 | 2 | 2 | 1 | X | 1 | 1 | X | 2 | 2 | 2 | 1 | X | X | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | ||
43 | 14/15 | X | 1 | X | 1 | X | 1 | 1 | X | 1 | 1 | 1 | 1 | 2 | X | X | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
44 | 14/15 | X | X | X | 1 | 1 | 1 | 1 | X | 1 | 1 | 1 | 1 | 2 | X | X | X | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
45 | 16/17 | 2 | X | 1 | 1 | 1 | 2 | X | X | 1 | 1 | 1 | 1 | 2 | X | X | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
46 | 16/17 | 2 | 1 | X | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 1 | 1 | 2 | 1 | 2 | 1 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
47 | 16/17 | 1 | 1 | 1 | 1 | X | 1 | X | 1 | 2 | 1 | 1 | 1 | 2 | 1 | 2 | X | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
48 | 17/08 | X | 1 | 2 | 1 | 2 | X | X | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 2 | X | 1 | X | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
49 | 17/18 | 1 | X | 2 | 2 | 2 | 1 | X | X | 2 | 1 | 1 | 1 | 2 | 1 | 2 | X | 1 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
50 | 17/18 | 1 | X | 1 | 1 | X | X | 2 | 2 | X | 1 | 1 | 1 | 2 | 1 | 2 | X | X | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
51 | 18/19 | 1 | X | 1 | 2 | 2 | 1 | X | 1 | 1 | X | 1 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
52 | 18/19 | X | 1 | X | 1 | X | 1 | 1 | X | 1 | 1 | 1 | 1 | 2 | 1 | 2 | X | X | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
53 | 18/19 | X | X | X | 1 | 1 | 1 | 1 | X | 1 | 1 | 1 | 1 | 2 | 1 | 2 | X | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
54 | 19/20 | 2 | X | 1 | 1 | 1 | 2 | X | X | X | 1 | X | 1 | 2 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
55 | 19/20 | X | 1 | X | X | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 2 | X | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
56 | 19/20 | 1 | X | X | 1 | 2 | 1 | 2 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 2 | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
57 | 19/20 | X | 2 | X | 1 | 1 | X | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 2 | 2 | 1 | X | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
58 | 19/20 | 1 | 1 | 1 | 1 | 1 | 1 | X | X | X | X | 2 | X | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
59 | 19/20 | 1 | 2 | 1 | 2 | X | 1 | 1 | 2 | X | 1 | 1 | 1 | 1 | X | 1 | X | 1 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | ||
60 | |||||||||||||||||||||||||
61 |
VBA Code:
Sub FillFormlas_SUMPRODUCT()
Sheets("Sumproduct").Select
Range("E4:X59003").ClearContents
Range("E4").Select
Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, "C").End(xlUp).Row
'-------------------------------------------------------------
Application.ScreenUpdating = False
'-------------------------------------------------------------
Range("E4:X" & lngLastRow).Formula = "=SUMPRODUCT(--($B$4:$B$5004=$C4),--($A$4:$A$5004=E$3))"
Range("E5:X" & lngLastRow) = Range("E5:X" & lngLastRow).Value 'Convert Values
'-------------------------------------------------------------
Application.ScreenUpdating = True
'-------------------------------------------------------------
End Sub
Thank you in advance
Regards,
Kishan