Count number of consecutive non blank cells in a column macro excel

Assuming you want the highest number of consecutive non-blank cells in a column
(for example, if there's 2 non blank cells in D1 and D2, then D3 is empty, but D4:D8 isn't, you want to show "5" for D4,D5,D6,D7 and D8)

Example code for column D.

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

Originally Posted by JasperD

doesn't that count all the filled cells, not specifically the consecutive ones?

Hm... I missed that part and thanks for the correction

Could you please let me know what is the OP's expected output for the below data (For example)

	D
1
2	5
3	5
4	5
5
6
7
8	4
9	4
10
11	6
12	6
13	6
14	6
15	6
16
17	1
18
19
20
21