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

Hi Miguel,

i have tried your suggestion VBA coding, in one of my orginial sheet but nothing appearing.

Regards, Atif

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,

thank you everyone for your great support