Trusted Members

December 20, 2019

November 28, 2020

thanks Purfleet,

but here we need to separate counting if the 3 blank cells in a row, so we need to counts, how many times the 3 or more blank rows appear.

secondly, what is the formula used, as this is just a sample data and the actually data is bit bigger then what i have shared, so i need to copy and add them in different sheet,

Regards, Atif

Trusted Members

December 20, 2019

November 28, 2020

thank you Purfleet and Miguel for your reply, yes we need to count consecutive 3 blank rows and consider as "1". now the sheet provide answer 2 for first rows, as it should count 3rows as 1 and if again 3rows and then again 1. but if there are 6 rows together then formula count 2, so each 3 blank rows consider "1".

i hope it is clear.

if any question, please let us know.

February 20, 2020

Hello,

```
please clarify what you really want,
in your Post 1, you say
```

" here we need to count the consecutive blank cells only, which appear to be continues 3 times or more"

this means cells on the same line, and that's what my macro does for each line! for every 3 consecutive empty cells or more, counts 1

```
and the Purfleet macro, does very well the empty cell count in the line
```

now you say in your Post 6 "yes we need to count consecutive 3 blank rows and consider as "1"" that means counting consecutive empty lines and not cells on one line!your message in Post 6 contradicts your message in Post 1!Miguel,

November 28, 2020

Dear Miguel,

yes, we need to count the consecutive 3 cells in a row to be consider as 1, but when the empty cells reached to 6 (which is dubble of 3), macro count them as 2. same for for 9, if the count reached to 9 consecutive cells, macros them as 3.

What Purfleet macro does that, it still count "1" if the number goes above than 3 consective rows,

June 25, 2020

Hi Atif,

maybe not the most elegant of solutions, but I've managed to find a solution without VBA

sometimes we use helper *columns* to achieve our goal, this uses a helper sheet!

as you add extra rows to your Utilisation table, you'll need to extend column A too (this counts your number of distinct 3-blank periods)

if you add extra columns to your Utilisation table, then you'll need to extend the helper sheet too

everything else should take care of itself

anyway, I believe this addresses your original need, do let me know

take care,

jim

February 20, 2020

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,

June 25, 2020

tried to edit my post but Miguel cut me off!

updated post here, and this time WITH an attached file!

Hi Atif,

maybe not the most elegant of solutions, but I've managed to find a solution without VBA

sometimes we use helper *columns* to achieve our goal, this uses a helper sheet!

as you add extra rows to your Utilisation table, you'll need to extend column A too (this counts your number of distinct 3-blank periods)

if you add extra columns to your Utilisation table, then you'll need to extend row 2 of the helper sheet too

everything else should take care of itself (I tried to get column A and row 2 to spill as well, but couldn't get it to work properly)

anyway, I believe this addresses your original need, do let me know

take care,

jim

June 25, 2020

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

November 28, 2020

Hi Jim,

sorry, my mistake. i havent looked at the helper sheet.

just looked at the helper sheet and there are lots of formula's. appreciate if you can support me, how to add them in my original data sheet?

i have attached my original data sheet for one row.

Regards, Atif

February 20, 2020

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**

1 Guest(s)