CODEWITHSUNDEEP
excelvba
Daniel Faith
Daniel Faith

Reputation: 1

Loop through column counting cells that are not blank

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

Upvotes: 0

Views: 760

Answers (1)

pnuts
pnuts

Reputation: 59475

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.

Upvotes: 0

Related Questions