count consecutive blank cells|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hello,

looking at the next image, the empty cells in each line between the column ("E) and the column (" AD ") were highlighted in red

the sequence of the empty cells is:

row 2 - consecutive empty cells => 3;18
row 3 - consecutive empty cells => 1;2;1;3;1
row 4 - consecutive empty cells => 2;1;5;1;1;1
row 5 - consecutive empty cells => 5;2;6;4
row 6 - consecutive empty cells => 1
row 7 - consecutive empty cells => 26
row 8 - consecutive empty cells => 21;4
row 9 - consecutive empty cells => 1
row 10 - consecutive empty cells => 26
row 11 - consecutive empty cells => 1
row 12 - consecutive empty cells => 26

do you want multiples of 3 empty cells in a row? 

example for row 7, you have 26 consecutive empty cells in a row, (26/3 = 8.6 ...), you want it to appear in this case 8 ?

example for row 5, you have (5;2;6;4) consecutive empty cells, you want it to appear in this case 4 ((5=1 + 2=0 + 6=2 + 4=1) = 4)?

Miguel,

Hi Atif,

did you just try to add that formula to your original workbook?

My solution relies on having the additional helper sheet and various other attributes from the workbook I attached
You may want to add your data to that?

I wish I could find a one-formula solution to your problem, but I'm not sure that's practical

jim

Hello,

if you want VBA version:

put the following in a standard module

Public Sub Count_consecutive_empty_cells_in_row_2()

Dim sht As Worksheet
Dim LastRow As Long
Dim i As Integer, nCount As Integer
Dim rRange As Range, rng As Range
Dim storeC As Integer

Set sht = Application.ThisWorkbook.Worksheets("Distance")
sht.Activate

LastRow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row

For i = 2 To LastRow
     If Not rRange Is Nothing Then Set rRange = Nothing
     nCount = 0
     Set rRange = sht.Range("E" & i & ":" & "AD" & i)
     For Each rng In rRange
         If rng.Value = "" Then
             nCount = nCount + 1
             Debug.Print nCount
             If nCount >= 3 Then storeC = storeC + 1: nCount = 0
         Else
             nCount = 0
         End If
     Next rng
     sht.Range("A" & i).Value = storeC
     storeC = 0
Next i

If Not rRange Is Nothing Then Set rRange = Nothing
If Not sht Is Nothing Then Set sht = Nothing

End Sub

on the excel sheet with a command button

Option Explicit

Private Sub CommandButton2_Click()
     Call Count_consecutive_empty_cells_in_row_2
End Sub

Hello,

attached sample file

your excel file needs to be saved with permissions for macros, that is, with the extension (.xlsm)


confirm that these references are active in your VBE project:

review what you’re doing wrong when trying to replicate my macro, because everything works

Miguel,