# Count blanks with criteria

#### jjrrcc11

##### New Member
Hello

I wish to count blank cells, presumably using =COUNTBLANK but I wish to only count the blanks prior to the final column with a value in.

So for example

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

This is a 2000+ row spreadsheet and each row may have a different last value point...

Thanks in advance

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### BarryL

##### Well-known Member
maybe a UDF?

Code:
Function Count_The_Blanks(roww As Long)
Dim lc As Long, firstcount As Long
lc = Cells(roww, Columns.Count).End(xlToLeft).Column
firstcount = WorksheetFunction.CountBlank(Range(Cells(roww, 1), Cells(roww, lc)))
Count_The_Blanks = firstcount
End Function

#### jimrward

##### Well-known Member
Thinking laterally the number of blanks will be the column number of the first non blank cell plus or minus 1 so possibly MATCH will do the trick, away from PC at the moment on tablet so cannot verify

Is the first non blank cell numerical data or text or possibly a mixture

Last edited:

#### jjrrcc11

##### New Member
maybe a UDF?

Code:
Function Count_The_Blanks(roww As Long)
Dim lc As Long, firstcount As Long
lc = Cells(roww, Columns.Count).End(xlToLeft).Column
firstcount = WorksheetFunction.CountBlank(Range(Cells(roww, 1), Cells(roww, lc)))
Count_The_Blanks = firstcount
End Function

UDF? Sorry I do not understand. Where do I enter the above code? Thanks

Thinking laterally the number of blanks will be the column number of the first non blank cell plus or minus 1 so possibly MATCH will do the trick, away from PC at the moment on tablet so cannot verify

Is the first non blank cell numerical data or text or possibly a mixture

All data is in date format, sorry I should have said.

#### jimrward

##### Well-known Member
assuming nothing in column A at all and starting on row 2 until your first non blank try the following

=MATCH(TRUE,INDEX((2:2<>0),0),0)-1

and drag down

or =MATCH(TRUE,INDEX((2:2<>""),0),0)-1

this gives the column of the first non blank value so we subtract 1 to get the blank count

Last edited:

#### jimrward

##### Well-known Member
second option is probably better to stop errors

#### jjrrcc11

##### New Member
assuming nothing in column A at all and starting on row 2 until your first non blank try the following

=MATCH(TRUE,INDEX((2:2<>0),0),0)-1

and drag down

or =MATCH(TRUE,INDEX((2:2<>""),0),0)-1

this gives the column of the first non blank value so we subtract 1 to get the blank count

Thanks for your time on this... Could you explain to me how it works/how I can alter? As I would like this to start on Column Z and run to Column CW... and starting on row 2 like you said.

Thanks

#### jimrward

##### Well-known Member
Starting in column Z you will need to change the 2:2 to Z2:CW2, 2:2 was a method to use the whole row, I think the -1 will still hold true at the end of the formula

Replies
4
Views
706
Replies
23
Views
977
Replies
2
Views
445
Replies
5
Views
251
Replies
7
Views
556

Threads
1,191,273
Messages
5,985,691
Members
439,974
Latest member
sjoerdbosch

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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