Count blanks with criteria

[jjrrcc11]

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

 

[BarryL]

maybe a UDF?

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]

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

 

[jjrrcc11]

maybe a UDF?

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.

 

[BarryL]

User Defined Functions (UDFs) In Excel VBA. Excel Training VBA lesson 21

ROWW is equal to the Row number e.g. 1 , 2, 3 etc

formula for first row would be

=Count_The_Blanks(1)

 

[jjrrcc11]

User Defined Functions (UDFs) In Excel VBA. Excel Training VBA lesson 21

ROWW is equal to the Row number e.g. 1 , 2, 3 etc

formula for first row would be

=Count_The_Blanks(1)

Sorry I can't seem to get the above to work.

Is there anyway it can be achieved just through the use of formulas? I would have thought an IF could be used somehow, where it checks the furthest value to the right and only counts blanks before it?

 

[jimrward]

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

 

[jimrward]

second option is probably better to stop errors

 

[jjrrcc11]

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]

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