• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

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

You are here: Home / count consecutive blank cells|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…count consecutive blank cells
sp_PrintTopic sp_TopicIcon
count consecutive blank cells
Avatar
Atif iqbal
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 28, 2020
sp_UserOfflineSmall Offline
1
November 28, 2020 - 1:37 am
sp_Permalink sp_Print

Team,

Please find the attached data sheet, here we need to count the consecutive blank cells only, which appear to be continues 3 times or more.

Actually, this is just short example, as we need to calculate the amount of data is huge.

Regards, AtifLaughLaughLaughLaughLaughLaughLaughLaughLaughLaugh

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
November 28, 2020 - 12:28 pm
sp_Permalink sp_Print

try this as a starting point - ranges will need updating

Avatar
Atif iqbal
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 28, 2020
sp_UserOfflineSmall Offline
3
November 28, 2020 - 2:40 pm
sp_Permalink sp_Print

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

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
4
November 28, 2020 - 9:35 pm
sp_Permalink sp_Print

Okay that wasnt clear in the question.

Do you want to count the cells that are blank (so the first row would be would be  3,17) or just the number of gaps there are (2)?

Its not a formula it was done in VBA - you can see the code by pressing alt+ f11

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
5
November 29, 2020 - 2:42 am
sp_Permalink sp_Print

Hello,

added a userform with result by msgbox, and added a command button on the sheet with result in column ("A")

attached test file

Miguel,
Avatar
Atif iqbal
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 28, 2020
sp_UserOfflineSmall Offline
6
November 29, 2020 - 12:04 pm
sp_Permalink sp_Print

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.

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
7
November 29, 2020 - 5:55 pm
sp_Permalink sp_Print sp_EditHistory

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,

Avatar
Atif iqbal
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 28, 2020
sp_UserOfflineSmall Offline
8
November 29, 2020 - 10:34 pm
sp_Permalink sp_Print

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,

Avatar
jab che
Member
Members
Level 0
Forum Posts: 16
Member Since:
June 25, 2020
sp_UserOfflineSmall Offline
9
November 30, 2020 - 2:09 am
sp_Permalink sp_Print

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

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
10
November 30, 2020 - 2:10 am
sp_Permalink sp_Print

Hello,

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

Capturar-1.JPGImage Enlarger

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,

sp_PlupAttachments Attachments
  • sp_PlupImage Capturar-1.JPG (49 KB)
Avatar
jab che
Member
Members
Level 0
Forum Posts: 16
Member Since:
June 25, 2020
sp_UserOfflineSmall Offline
11
November 30, 2020 - 2:33 am
sp_Permalink sp_Print

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

Avatar
Atif iqbal
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 28, 2020
sp_UserOfflineSmall Offline
12
November 30, 2020 - 11:56 am
sp_Permalink sp_Print

thank Jim,

Yes Miguel, you are right, 26 consecutive empty cells in a row, (26/3 = 8.6 ...)

 

Regards, Atif

Avatar
Atif iqbal
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 28, 2020
sp_UserOfflineSmall Offline
13
November 30, 2020 - 11:59 am
sp_Permalink sp_Print

Jim,

i have tried to use your formula "=COUNTIF(Helper!2:2,3)" in original sheet and get error. is there something changed any where?

Regards, Atif

Avatar
jab che
Member
Members
Level 0
Forum Posts: 16
Member Since:
June 25, 2020
sp_UserOfflineSmall Offline
14
November 30, 2020 - 7:57 pm
sp_Permalink sp_Print

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

Avatar
Atif iqbal
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 28, 2020
sp_UserOfflineSmall Offline
15
November 30, 2020 - 8:17 pm
sp_Permalink sp_Print

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

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
16
November 30, 2020 - 10:04 pm
sp_Permalink sp_Print

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

 

capture.JPGImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage capture.JPG (50 KB)
Avatar
Atif iqbal
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 28, 2020
sp_UserOfflineSmall Offline
17
December 2, 2020 - 3:17 pm
sp_Permalink sp_Print

Hi Miguel,

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

Regards, Atif

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
18
December 2, 2020 - 8:09 pm
sp_Permalink sp_Print

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:

img1-1.JPGImage Enlarger

 

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

 

Miguel,

sp_PlupAttachments Attachments
  • sp_PlupImage img1-1.JPG (26 KB)
Avatar
Atif iqbal
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 28, 2020
sp_UserOfflineSmall Offline
19
December 4, 2020 - 3:43 pm
sp_Permalink sp_Print

thank you everyone for your great support

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: baber Tufail
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
drsven
Annie Witbrod
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27211

 

Member Stats:
Guest Posters: 49
Members: 31880
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.