Find last then count

[fromspenny]

Hi all could use your help on this one
What I am trying to do is find the last w and from that point count the remaining items in the list so in this example the answer would be 3 thanks in advance

Excel Workbook
	A
1	L
2	W
3	D
4	L
5	W
6	L
7	L
8	D
9	W
10	W
11	W
12	L
13	D
14	L
15	D
16	L
17	W
18	L
19	L
20	W
21	W
22	D
23	D
24	L
25	D
26	D
27	W
28	D
29	L
30	L
31	W
32	L
33	D
34	D
35
36
37
38
Sheet1

 

[mirabeau]

maybe a single line of VBA?

MsgBox Range("A:A")(Rows.Count).End(3).Row - Range("A:A").Find("W", searchdirection:=xlPrevious).Row

 

[T. Valko]

Hi all could use your help on this one
What I am trying to do is find the last w and from that point count the remaining items in the list so in this example the answer would be 3 thanks in advance

Excel Workbook
	A
1	L
2	W
3	D
4	L
5	W
6	L
7	L
8	D
9	W
10	W
11	W
12	L
13	D
14	L
15	D
16	L
17	W
18	L
19	L
20	W
21	W
22	D
23	D
24	L
25	D
26	D
27	W
28	D
29	L
30	L
31	W
32	L
33	D
34	D
35
36
37
38
Sheet1

Assuming there will always be at least one instance of "W"...

=COUNTA(A100:INDEX(A:A,LOOKUP(2,1/(A1:A100="w"),ROW(A1:A100))+1))

 

[fromspenny]

Thanks T.Valko works like a charm

 

[T. Valko]

Thanks T.Valko works like a charm

You're welcome. Thanks for the feedback!