Count blanks prior to final cell with value

[jjrrcc11]

Hello

I need to count the number of blank cells, but wish to only count the blanks prior to the final column with a value in, even if there are blanks after this column.

So for example:

Row 2's last value is in column BD, so I wish to count the number of blanks between A and BD... despite there being further blanks after BD... I do not wish for these to be included.
Row 3's last value is in BN... so similarly I only wish for the blanks prior to BN to be counted.

This is a 7000+ row spreadsheet and each row may have a different last value point... The value is always a date. I'd ideally like the sum of blanks for each row to be displayed at the end of each row, say in column DE.

Ideally a formula would be best if it can be accomplished with one, rather than VB.

Thanks in advance

 

[godsaaint]

Insert in DE1 and drag down(Confirm with CTRL-SHIFT-ENTER):

=COUNTBLANK(INDIRECT("A1:"&ADDRESS(ROW(A1),LARGE(IF(A1:DD1<>"",COLUMN(A1:DD1)),1))))

 

[Aladin Akyurek]

In DE2 enter and copy down:

=COUNTBLANK(A2:INDEX(A2:DD2,MATCH(9.99999999999999E+307,A2:DD2)))

 

[BarryL]

my only way would be through VBA

Function totalblanks(r As Long)
Dim lc1 As Long, lc2 As Long
lc1 = Cells(r, Columns.Count).End(xlToLeft).Column - 1
Do While Cells(r, lc1).Value = ""
lc1 = lc1 - 1
Loop
totalblanks = WorksheetFunction.CountBlank(Range(Cells(r, 1), Cells(r, lc1)))
End Function

enter into cell as below

=totalblanks(ROW())

 

[jjrrcc11]

Thanks all for replies.

In DE2 enter and copy down:

=COUNTBLANK(A2:INDEX(A2:DD2,MATCH(9.99999999999999E+307,A2:DD2)))

This works perfectly, great stuff thanks.

 

[Aladin Akyurek]

Thanks all for replies.



This works perfectly, great stuff thanks.

You are welcome.