Try this:
![]()
Sub CountNumBlanks2() Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sample") Dim c As Range, rng As Range, i As Integer Set rng = ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp)) For Each c In rng If Len(c.Value) > 0 Then If Application.CountA(Range(rng(1, 1), c(0, 1))) > 0 Then c(1, 2).Value = i i = Empty Else c(1, 2).Value = "N/A" 'CVErr(xlErrNA) i = Empty End If Else i = i + 1 End If Next c End Sub
Bookmarks