Is there a way to count blank "rows" in Excel 2016, not just blank cells. I have a document where I need to know how many rows within a range of data do not have anything in them. Hopefully this makes sense. Thank you!

3 Answers

VBA is not required. Helper columns are not required. Even array entering a formula is not required!

Enter the following formula in H2:

=SUM(--(MMULT(--(A2:F11<>""),ROW(INDEX(H:H,1):INDEX(H:H,COLUMNS(A2:F11))))=0))

The formula works by multiplying the matrix of the check for non-blank of the entire range with a column vector of non-zeroes of the same length as the number of columns of the range. This results in a column vector of values which are greater than zero if any of the cells of the appropriate row is non-blank. Summing the check for zero of each of these values results in the number of blank rows.

1

How it works:

• In Cell E2 write this Formula: =QUOTIENT(COUNTBLANK(B2:D2),3)& fill it down.

Note, 3 represents "total columns" in data range, like B, C & D are 3. You can change it as many columns included in data rage.

• Write this formula in Cell E11 =Sum(E2:E10)

If you are able to use VBA then this is fairly straight forward. This code will take the selected range as an input. It then selects each row individually and checks if it is empty. If it is, then it increments the counter by 1. At the end, it re-selects your original range and gives a message box with the number of rows which are completely empty. It would require some modification to select only part of the row, but that should also be fairly simple to implement.

Public Sub countBlanks()
Dim Rowcount, currentRow, i, countBlanks As Integer
Dim Selected As Variant
Selected = Selection.Address
Rowcount = Selection.Rows.Count
currentRow = Selection.Rows(1).Row
For i = currentRow To currentRow + Rowcount - 1 Rows(i).Select If WorksheetFunction.CountA(Selection) = 0 Then countBlanks = countBlanks + 1
Next i
Range(Selected).Select
MsgBox countBlanks
End Sub

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy