Count blanks prior to final cell with value

jjrrcc11

New Member
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.

Last edited:

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

godsaaint

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

MrExcel MVP
In DE2 enter and copy down:

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

BarryL

Well-known Member
my only way would be through VBA

Code:
``````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

New Member
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.

MrExcel MVP
Thanks all for replies.

This works perfectly, great stuff thanks.

You are welcome.

Replies
13
Views
472
Replies
4
Views
170
Replies
4
Views
162
Replies
0
Views
171
Replies
5
Views
297

1,190,693
Messages
5,982,324
Members
439,773
Latest member
tyruschen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back