excel vba count number of blank cells below each non empty cells

Question

I am quite new with Excel VBA. What I want to do is create a VBA loop that will count the number of cells below each non empty cells.

col c   col d
abc     1
        2
        3
        4
abc     5
        6
        7
        8
        9
        10

Here's what I've tried so far:

Sub test()

Dim a, b, c, d, i, k As Integer
Dim y As Range

k = Worksheets("Sheet2").Range("d" & Rows.Count).End(xlUp).Row '13
a = 3
b = 3

For i = 4 To k                        
    If IsEmpty(Cells(i, 3)) = True Then        
        c = c + 1                    
    Else        
        d = d + 1            
    End If    
Next

MsgBox c
MsgBox d

End Sub

Your question is not clear. What is your expected output? Explain with logic. — Harun24HR
sorry. what i want to do is get the number of blank rows under each non blank row. say for example the first abc would result to 3 then the second abc will result to 5 — lsatienz
@lsatienz actualy the second result would be infinity (or something very high) since there is no cells at the end of Column C, right ? how would the code know where to stop for the last abc ? — Shai Rado

user3598756 user3598756 · Accepted Answer · 2016-12-03T08:09:35

try this

Sub main2()
    Dim iArea As Long
    Dim rng As Range

    With Worksheets("Sheet2")
        Set rng = .Range("D4", .Cells(.Rows.count, "D").End(xlUp)).Offset(, -1) '<--| set the range of its column "C" cells corresponding to its column "D" ones from row 2 down to last not empty one
        With rng.SpecialCells(xlCellTypeConstants) '<--| reference not empty rng cells
            For iArea = 1 To .Areas.count - 1
                MsgBox .Parent.Range(.Areas(iArea).Cells(1, 1), .Areas(iArea + 1).Cells(1, 1).Offset(-1)).SpecialCells(xlCellTypeBlanks).count
            Next iArea
            MsgBox .Parent.Range(.Areas(iArea).Cells(1, 1), rng(rng.Rows.count)).SpecialCells(xlCellTypeBlanks).count
        End With
    End With
End Sub

excel vba count number of blank cells below each non empty cells

1 Answers