Find LastRow doesn't work for me.

[LenaH]

Hi all,

I have a list with some data I need to copy to a different sheet.

I declared a Variable as you can see, below. The direct window shows me 84 as value for my variable,
although the cells are empty. It should read 56 for my understanding. The empty cells have formulas in them.

Any ideas, why it's not working?
Lena

Tourney.xlsm
	D	E	F	G	H	I	J	K	L
5	Jochen Peters	1209	731	1058	1800	0	4798	USA 1	14732
6	Peter McNamara	727	1500	1289	0	0	3516	USA 1	14732
7	Christina Huisman	928	1466	728	0	0	3122	USA 1	14732
8	Eve Dori	1353	782	1161	0	0	3296	USA 1	14732
9	Angela Merkl	938	710	1271	0	0	2919	France 1	12960
10	Peter McNamara	727	1500	1289	0	0	3516	France 1	12960
11	Michael Chicken	849	847	1079	0	0	2775	France 1	12960
12	Guy Jensen	1166	1162	1422	0	0	3750	France 1	12960
13	Guy Jensen	1166	1162	1422	0	0	3750	France 2	3750
14								France 2	3750
15								France 2	3750
16								France 2	3750
17	Peter McNamara	727	1500	1289	0	0	3516	Germany 1	12641
18	Christina Huisman	928	1466	728	0	0	3122	Germany 1	12641
19	Gerd Rainberg	803	1230	1195	0	0	3228	Germany 1	12641
20	Michael Chicken	849	847	1079	0	0	2775	Germany 1	12641
21	Jochen Peters	1209	731	1058	1800	0	4798	Germany 2	13923
22	Christina Huisman	928	1466	728	0	0	3122	Germany 2	13923
23	Gerd Rainberg	803	1230	1195	0	0	3228	Germany 2	13923
24	Michael Chicken	849	847	1079	0	0	2775	Germany 2	13923
25	Jochen Peters	1209	731	1058	1800	0	4798	USA 2	14389
26	Steve Austin	928	1466	728	0	0	3122	USA 2	14389
27	Gerd Rainberg	803	1230	1195	0	0	3228	USA 2	14389
28	Peter Hoffmann	657	1162	1422	0	0	3241	USA 2	14389
29	Angela Merkl	938	710	1271	0	0	2919	Canada 2	12680
30	Ron Link	918	1230	1195	0	0	3343	Canada 2	12680
31	Steve Austin	928	1466	728	0	0	3122	Canada 2	12680
32	Eve Dori	1353	782	1161	0	0	3296	Canada 2	12680
33	Angela Merkl	938	710	1271	0	0	2919	Canada 3	12565
34	Steve Austin	928	1466	728	0	0	3122	Canada 3	12565
35	Gerd Rainberg	803	1230	1195	0	0	3228	Canada 3	12565
36	Eve Dori	1353	782	1161	0	0	3296	Canada 3	12565
37	Jochen Peters	1209	731	1058	1800	0	4798	Russia 1	14818
38	Ron Link	918	1230	1195	0	0	3343	Russia 1	14818
39	Peter McNamara	727	1500	1289	0	0	3516	Russia 1	14818
40	Frank Deyer	1180	710	1271	0	0	3161	Russia 1	14818
41	Frank Deyer	1180	710	1271	0	0	3161	Russia 2	12982
42	Eve Dori	1353	782	1161	0	0	3296	Russia 2	12982
43	Michael Chicken	849	847	1079	0	0	2775	Russia 2	12982
44	Guy Jensen	1166	1162	1422	0	0	3750	Russia 2	12982
45	Frank Deyer	1180	710	1271	0	0	3161	Poland 1	3161
46								Poland 1	3161
47								Poland 1	3161
48								Poland 1	3161
49	Jules Harding	798	782	1161	0	0	2741	Poland 2	5902
50	Frank Deyer	1180	710	1271	0	0	3161	Poland 2	5902
51								Poland 2	5902
52								Poland 2	5902
53	Angela Merkl	938	710	1271	0	0	2919	Compo Guano	12506
54	Ron Link	918	1230	1195	0	0	3343	Compo Guano	12506
55	Steve Austin	928	1466	728	0	0	3122	Compo Guano	12506
56	Christina Huisman	928	1466	728	0	0	3122	Compo Guano	12506
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
TeamStandingsTemplate

Cell Formulas
Range	Formula
D5	D5	=IF(B5="","",VLOOKUP($B$5,$N$157:$R$177,2,FALSE))
E5:E84	E5	=IF(($D5)="","",VLOOKUP($D5,PlayerPool!$D$2:$L$151,4,FALSE))
F5:F84	F5	=IF(($D5)="","",VLOOKUP($D5,PlayerPool!$D$2:$L$151,5,FALSE))
G5:G84	G5	=IF(($D5)="","",VLOOKUP($D5,PlayerPool!$D$2:$L$151,6,FALSE))
H5:H84	H5	=IF(($D5)="","",VLOOKUP($D5,PlayerPool!$D$2:$L$151,7,FALSE))
I5:I84	I5	=IF(($D5)="","",VLOOKUP($D5,PlayerPool!$D$2:$L$151,8,FALSE))
J5:J84	J5	=IF(D5="","",SUM(E5:I5))
K5:K8	K5	=$N$157
L5:L8	L5	=$C$5
D6	D6	=IF(B5="","",VLOOKUP($B$5,$N$157:$R$177,3,FALSE))
D7	D7	=IF(B5="","",VLOOKUP($B$5,$N$157:$R$177,4,FALSE))
D8	D8	=IF(B5="","",VLOOKUP($B$5,$N$157:$R$177,5,FALSE))
D9	D9	=IF(B9="","",VLOOKUP($B$9,$N$157:$R$177,2,FALSE))
K9:K12	K9	=$N$158
L9:L12	L9	=$C$9
D10	D10	=IF(B9="","",VLOOKUP($B$9,$N$157:$R$177,3,FALSE))
D11	D11	=IF(B9="","",VLOOKUP($B$9,$N$157:$R$177,4,FALSE))
D12	D12	=IF(B9="","",VLOOKUP($B$9,$N$157:$R$177,5,FALSE))
D13:D16	D13	=IF(B13="","",VLOOKUP($B$13,$N$157:$R$177,2,FALSE))
K13:K16	K13	=$N$159
L13:L16	L13	=$C$13
D17	D17	=IF(B17="","",VLOOKUP($B$17,$N$157:$R$177,2,FALSE))
K17:K20	K17	=$N$160
L17:L20	L17	=$C$17
D18	D18	=IF(B17="","",VLOOKUP($B$17,$N$157:$R$177,3,FALSE))
D19	D19	=IF(B17="","",VLOOKUP($B$17,$N$157:$R$177,4,FALSE))
D20	D20	=IF(B17="","",VLOOKUP($B$17,$N$157:$R$177,5,FALSE))
D21	D21	=IF(B21="","",VLOOKUP($B$21,$N$157:$R$177,2,FALSE))
K21:K24	K21	=$N$161
L21:L24	L21	=$C$21
D22	D22	=IF(B21="","",VLOOKUP($B$21,$N$157:$R$177,3,FALSE))
D23	D23	=IF(B21="","",VLOOKUP($B$21,$N$157:$R$177,4,FALSE))
D24	D24	=IF(B21="","",VLOOKUP($B$21,$N$157:$R$177,5,FALSE))
D25	D25	=IF(B25="","",VLOOKUP($B$25,$N$157:$R$177,2,FALSE))
K25:K28	K25	=$N$162
L25:L28	L25	=$C$25
D26	D26	=IF(B25="","",VLOOKUP($B$25,$N$157:$R$177,3,FALSE))
D27	D27	=IF(B25="","",VLOOKUP($B$25,$N$157:$R$177,4,FALSE))
D28	D28	=IF(B25="","",VLOOKUP($B$25,$N$157:$R$177,5,FALSE))
D29	D29	=IF(B29="","",VLOOKUP($B$29,$N$157:$R$177,2,FALSE))
K29:K32	K29	=$N$163
L29:L32	L29	=$C$29
D30	D30	=IF(B29="","",VLOOKUP($B$29,$N$157:$R$177,3,FALSE))
D31	D31	=IF(B29="","",VLOOKUP($B$29,$N$157:$R$177,4,FALSE))
D32	D32	=IF(B29="","",VLOOKUP($B$29,$N$157:$R$177,5,FALSE))
D33	D33	=IF(B33="","",VLOOKUP($B$33,$N$157:$R$177,2,FALSE))
K33:K36	K33	=$N$164
L33:L36	L33	=$C$33
D34	D34	=IF(B33="","",VLOOKUP($B$33,$N$157:$R$177,3,FALSE))
D35	D35	=IF(B33="","",VLOOKUP($B$33,$N$157:$R$177,4,FALSE))
D36	D36	=IF(B33="","",VLOOKUP($B$33,$N$157:$R$177,5,FALSE))
D37	D37	=IF(B37="","",VLOOKUP($B$37,$N$157:$R$177,2,FALSE))
K37:K40	K37	=$N$165
L37:L40	L37	=$C$37
D38	D38	=IF(B37="","",VLOOKUP($B$37,$N$157:$R$177,3,FALSE))
D39	D39	=IF(B37="","",VLOOKUP($B$37,$N$157:$R$177,4,FALSE))
D40	D40	=IF(B37="","",VLOOKUP($B$37,$N$157:$R$177,5,FALSE))
D41	D41	=IF(B41="","",VLOOKUP($B$41,$N$157:$R$177,2,FALSE))
K41:K44	K41	=$N$166
L41:L44	L41	=$C$41
D42	D42	=IF(B41="","",VLOOKUP($B$41,$N$157:$R$177,3,FALSE))
D43	D43	=IF(B41="","",VLOOKUP($B$41,$N$157:$R$177,4,FALSE))
D44	D44	=IF(B41="","",VLOOKUP($B$41,$N$157:$R$177,5,FALSE))
D45	D45	=IF(B45="","",VLOOKUP($B$45,$N$157:$R$177,2,FALSE))
K45:K48	K45	=$N$167
L45:L48	L45	=$C$45
D46	D46	=IF(B45="","",VLOOKUP($B$45,$N$157:$R$177,3,FALSE))
D47	D47	=IF(B45="","",VLOOKUP($B$45,$N$157:$R$177,4,FALSE))
D48	D48	=IF(B45="","",VLOOKUP($B$45,$N$157:$R$177,5,FALSE))
D49	D49	=IF(B49="","",VLOOKUP($B$49,$N$157:$R$177,2,FALSE))
K49:K52	K49	=$N$168
L49:L52	L49	=$C$49
D50	D50	=IF(B49="","",VLOOKUP($B$49,$N$157:$R$177,3,FALSE))
D51	D51	=IF(B49="","",VLOOKUP($B$49,$N$157:$R$177,4,FALSE))
D52	D52	=IF(B49="","",VLOOKUP($B$49,$N$157:$R$177,5,FALSE))
D53	D53	=IF(B53="","",VLOOKUP($B$53,$N$157:$R$177,2,FALSE))
K53:K56	K53	=$N$169
L53:L56	L53	=$C$53
D54	D54	=IF(B53="","",VLOOKUP($B$53,$N$157:$R$177,3,FALSE))
D55	D55	=IF(B53="","",VLOOKUP($B$53,$N$157:$R$177,4,FALSE))
D56	D56	=IF(B53="","",VLOOKUP($B$53,$N$157:$R$177,5,FALSE))
D57	D57	=IF(B57="","",VLOOKUP($B$57,$N$157:$R$177,2,FALSE))
K57:K60	K57	=$N$170
L57:L60	L57	=$C$57
D58	D58	=IF(B57="","",VLOOKUP($B$57,$N$157:$R$177,3,FALSE))
D59	D59	=IF(B57="","",VLOOKUP($B$57,$N$157:$R$177,4,FALSE))
D60	D60	=IF(B57="","",VLOOKUP($B$57,$N$157:$R$177,5,FALSE))
D61	D61	=IF(B61="","",VLOOKUP($B$61,$N$157:$R$177,2,FALSE))
K61:K64	K61	=$N$171
L61:L64	L61	=$C$61
D62	D62	=IF(B61="","",VLOOKUP($B$61,$N$157:$R$177,3,FALSE))
D63	D63	=IF(B61="","",VLOOKUP($B$61,$N$157:$R$177,4,FALSE))
D64	D64	=IF(B61="","",VLOOKUP($B$61,$N$157:$R$177,5,FALSE))
D65	D65	=IF(B65="","",VLOOKUP($B$65,$N$157:$R$177,2,FALSE))
K65:K68	K65	=$N$172
L65:L68	L65	=$C$65
D66	D66	=IF(B65="","",VLOOKUP($B$65,$N$157:$R$177,3,FALSE))
D67	D67	=IF(B65="","",VLOOKUP($B$65,$N$157:$R$177,4,FALSE))
D68	D68	=IF(B65="","",VLOOKUP($B$65,$N$157:$R$177,5,FALSE))
D69	D69	=IF(B69="","",VLOOKUP($B$69,$N$157:$R$177,2,FALSE))
K69:K72	K69	=$N$173
L69:L72	L69	=$C$69
D70	D70	=IF(B69="","",VLOOKUP($B$69,$N$157:$R$177,3,FALSE))
D71	D71	=IF(B69="","",VLOOKUP($B$69,$N$157:$R$177,4,FALSE))
D72,D84,D80,D76	D72	=IF(B69="","",VLOOKUP($B$13,$N$157:$R$177,5,FALSE))
D73	D73	=IF(B73="","",VLOOKUP($B$73,$N$157:$R$177,2,FALSE))
K73:K76	K73	=$N$174
L73:L76	L73	=$C$73
D74	D74	=IF(B73="","",VLOOKUP($B$73,$N$157:$R$177,3,FALSE))
D75	D75	=IF(B73="","",VLOOKUP($B$73,$N$157:$R$177,4,FALSE))
D77	D77	=IF(B77="","",VLOOKUP($B$77,$N$157:$R$177,2,FALSE))
K77:K80	K77	=$N$175
L77:L80	L77	=$C$77
D78	D78	=IF(B77="","",VLOOKUP($B$77,$N$157:$R$177,3,FALSE))
D79	D79	=IF(B77="","",VLOOKUP($B$77,$N$157:$R$177,4,FALSE))
D81	D81	=IF(B81="","",VLOOKUP($B$81,$N$157:$R$177,2,FALSE))
K81:K84	K81	=$N$176
L81:L84	L81	=$C$81
D82	D82	=IF(B81="","",VLOOKUP($B$81,$N$157:$R$177,3,FALSE))
D83	D83	=IF(B81="","",VLOOKUP($B$81,$N$157:$R$177,4,FALSE))

 

[MARK858]

They are not empty. They have a formula returning "". Try..

Last = Worksheets("TeamStandingsTemplate").Columns(4).Find("*", , xlValues, , xlByRows, xlPrevious).Row

and declare Last as Long not Integer.

 

[LenaH]

That's it - wonderful! Thank you, Mark!

Lena

 

[MARK858]

You're welcome.