Loop through column counting cells that are not blank

0
votes

I am trying to loop through a column and count the number of cells that are not blank. The process that it must follow are:

  1. Start at first cell and count non blank cells until you get to two consecutive blank cells

  2. STOP at this point and return the value of the count to a cell on the sheet

  3. Start Count again at next non blank cell and repeat 1. and 2. until you have reached the end of the data

Extra: if i could also return the date in the row that relates to the first non blank and also the first blank (from the point at which you find the first two blank cells)

If you need more information please ask. I presume VBA would be much better at doing this?

Thanks.

Code so far is:

Sub Test1()

Range("I3").Select

Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1, 0))

Dim iVal As Integer

iVal = Application.WorksheetFunction.CountIf(Range("I:I"), "TRUE")

ActiveCell.Offset(2, 0).Select

  Loop

End Sub

1
excelvba
what language and/or lib do you use and where did you get so far in your code ? can you show it ? – lu1her
I am just using Visual Basic in Excel 2016. – Daniel Faith

1 Answers

0
votes

As you mention I presume VBA would be much better at doing this? I am assuming you would contemplate a formula solution.

Assuming data is in ColumnA starting with a single blank cell, in B2: 

=IF(AND(ISBLANK(A2),ISBLANK(A3)),COUNTA(A$1:A2)-SUM(B$1:B1),"")

dragged down until 0 appears.