Thread: Count blank cells within a range not including fully blank rows

Hi

Ok, I can count the blank cells withiin a range using

=COUNTBLANK(C6:AD2506)

But I dont want it to count the cells if the entire row, within that cell, i.e. C6:AD6, is blank.

It should only count the blank cells within a row if there has been some data entered on that row..provided it has been entered within the specified range.

Any help?

ok i have put the helper column on another sheet....its worked fine.

BUT..i dont understand what the other formula is doing, I have use:

=SUM(AM6:AM2506)-SUMIF(AM6:AM2506,COLUMNS('enter data'!C6:'enter data'!AD6))

and its comming up with what seems to be a completley unrelated number.

could you please explain that formula abit better?

thanks??

Thanks that works perfectly.

On to the next level:

I need to display that value in a text box on a user form.

This user form will have a button on it which takes the user to the 1st empty cell within that range (again not including the completley empty rows)

any idea how to do these?

You can also use a user defined function to count the blank cells in non blank rows:

Function CntBlanks(myRng As Range)
    Dim rng As Range
    Dim rCount As Long
    Dim cCount As Long
    Dim CntTotal As Long
    Dim cel As Range
    
    rCount = myRng.Rows.Count
    Set rng = myRng.Range("A1").Resize(rCount)
    cCount = myRng.Columns.Count
    
    CntTotal = 0
    For Each cel In rng
        If Application.WorksheetFunction.CountA(cel.Resize(, cCount)) <> 0 Then
            CntTotal = CntTotal + Application.WorksheetFunction.CountBlank(cel.Resize(, cCount))
        End If
    Next cel
    
    CntBlanks = CntTotal
    
End Function

Anywhere on your worksheet, you can enter

=cntblanks(C6:AD2506)

Let me know if that helps.

thanks for the replys. lets say I only needed to do the same formula but this time not including columns AC,AB,Y,X,U,V,W,T,S,P,E,D.

What would i have to change:

=SUM(AM6:AM2506)-SUMIF(AM6:AM2506,COLUMNS('enter data'!C6:'enter data'!AD6))

to?

Hi


This works very well, thankyou

Unfortunatley this formula does not miss out the fully blank rows within the data range. I do not want it to count rows that have no data between cells C:AD.

I have a sheet with 28 columns to be filled in by the user. 14 of these columns are mandatory so the formula is to count how many mandatory fields they have not filled in.

There are 2500 rows which can be filled in so I only want to count the blank mandatory fields in the rows which have been party or fully filled in.
Otherwise if the user only filled in 100 rows, when i used the function it would bring up 2400 rows worth of blank cells.


So if there is no data on a row (within the C6:AD2506 range) then the blank mandatory fields within that row should not be added to the count.

This function will be used to check errors on the data that they have filled in, not nessasserily the entire range.

Does that make it any clearer?

If any data at all has been entered by the user into the row then the blank mandatory fields should be included in the count.
The user will enter thier data horizontally, one row = one product. So if there is no data at all in ANY of the fields on a row then the user obviously has not got that far down the sheet yet, therefore the blank madatory fields within the blank row should NOT be included in the count.

Heres the attachment..

The data is entered from C6:AD2506, the red colums are mandatory and the yellow are not. The rows are labeled 'Item1' 'Item2' etc, each Item = one product. One product per row.