Need to count blank cells between two non-blank cells

[mynameisnotbob]

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.

[TABLE="width: 596"]
<colgroup><col span="5"><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[TD]Saturday[/TD]
[TD]Sunday[/TD]
[TD]Monday[/TD]
[/TR]
[TR]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6:00[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7:00[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8:00[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9:00[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10:00[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11:00[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12:00[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13:00[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]14:00[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]15:00[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16:00[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]17:00[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]18:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[TD]36[/TD]
[TD]24[/TD]
[TD][/TD]
[TD][/TD]
[TD]48[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[footoo]

Here's a macro :

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

 

[footoo]

To run automatically :

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

 

[mynameisnotbob]

Thanks so much!

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

 

[footoo]

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]

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]

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.

 

[footoo]

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]

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]

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

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.