# Need to count blank cells between two non-blank cells

#### mynameisnotbob

##### New Member
I have a sheet for the days of the week, and hours within the day. Every hour worked has a '1' in the corresponding cell. Every hour not worked is blank. I need to calculate the number of hours between worked hours.

 Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday 0:00 1 1:00 1 2:00 1 3:00 1 4:00 1 5:00 1 6:00 1 1 1 7:00 1 1 1 8:00 1 1 1 9:00 1 1 1 10:00 1 1 1 11:00 1 1 1 12:00 1 1 1 13:00 1 1 1 14:00 1 1 1 15:00 1 1 1 16:00 1 1 1 17:00 1 1 1 18:00 1 19:00 1 20:00 1 21:00 1 22:00 1 23:00 1 30 36 24 48

<colgroup><col span="5"><col><col span="3"></colgroup><tbody>
</tbody>

What formula can I use to count the number of blank cells from the last "1" to the next "1" in consecutive hours.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### footoo

##### Well-known Member
Here's a macro :
Code:
``````Sub v()
Dim r%, c%, rng As Range, area As Range
Application.ScreenUpdating = False
With [B26:I26]
.Formula = "=IF(OR(AND(A25<>"""",B2<>""""),COUNTA(B2:B25)=0),"""",""E"")"
.Value = .Value
End With
r = 26
For c = 2 To 9
Cells(2, c).Resize(24).Copy Cells(r, 1)
r = r + 24
Next
Set rng = Range("A2:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeBlanks)
For Each area In rng.Areas
[B26:I26].Find("E").Value = area.Count
Next
[A26].Resize(8 * 24).ClearContents
End Sub``````

• mynameisnotbob

#### footoo

##### Well-known Member
To run automatically :
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim r%, c%, rng As Range, area As Range
If Not Intersect(Target, [B2:I25]) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
With [B26:I26]
.Formula = "=IF(OR(AND(A25<>"""",B2<>""""),COUNTA(B2:B25)=0),"""",""E"")"
.Value = .Value
End With
r = 26
For c = 2 To 9
Cells(2, c).Resize(24).Copy Cells(r, 1)
r = r + 24
Next
Set rng = Range("A2:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeBlanks)
For Each area In rng.Areas
[B26:I26].Find("E").Value = area.Count
Next
[A26].Resize(8 * 24).ClearContents
Application.EnableEvents = True
End If
End Sub``````

Last edited:
• mynameisnotbob

#### mynameisnotbob

##### New Member
Thanks so much!

Is there a non-macro solution? My company frowns upon using macros saved on the server...

#### footoo

##### Well-known Member
Thanks so much!

Is there a non-macro solution? My company frowns upon using macros saved on the server...
I can't think one. Maybe someone else can.

• mynameisnotbob

#### mynameisnotbob

##### New Member
If using the macro, if I needed this to go out for a full year of days, would I just change :I26 to :ZZ26? (or whatever the columns go out to...)

#### Rick Rothstein

##### MrExcel MVP
Is there a non-macro solution? My company frowns upon using macros saved on the server...
Here is an array-entered** formula that appears to work. I would be surprised if someone does not find a more efficient formula, but this was all I could come up with. Place the following formula in cell B26 and copy it across...

=IF(COUNTBLANK(B2:B25)=24,"",SUM(IFERROR(MATCH(1,--(LEN(B2:B25)>0),0)-1,24))+COUNTBLANK(\$A2:A25)-SUM(\$A26:A26))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Note: If you want to place the formula on a different row, then change the red numbers to that new row number.

Last edited:
• mynameisnotbob

#### footoo

##### Well-known Member
If using the macro, if I needed this to go out for a full year of days, would I just change :I26 to :ZZ26? (or whatever the columns go out to...)
Presume you don't need this since you now have a formula

• mynameisnotbob

#### mynameisnotbob

##### New Member
Thanks to both of you!! Appreciate everything.

The formula works.

For curiosty sake, I'd still like to know the macro... but its no longer needed.

#### footoo

##### Well-known Member
For curiosty sake, I'd still like to know the macro... but its no longer needed.
Code:
``````Sub v()
Dim lc%, r%, c%, rng As Range, area As Range
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
With [B26].Resize(, lc - 1)
.Formula = "=IF(OR(AND(A25<>"""",B2<>""""),COUNTA(B2:B25)=0),"""",""E"")"
.Value = .Value
End With
r = 26
For c = 2 To lc
Cells(2, c).Resize(24).Copy Cells(r, 1)
r = r + 24
Next
Set rng = Range("A2:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeBlanks)
For Each area In rng.Areas
[B26].Resize(, lc - 1).Find("E").Value = area.Count
Next
[A26].Resize((lc - 1) * 24).ClearContents
End Sub``````
Could simplify the macro by making use of the formula solution.