VBA : Number of Blank Cells in a Range

[Guest]

How to get the number of Blanks rows in a range in VBA

 

[Bob Phillips]

application.countblank(range("A1:H10"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi
try
msgbox application.countblank(activesheet.range("A1:A100"))

or
msgbox application.countblank(selection)

 

[Tom Ogilvy]

If the rng contains either blanks or constant values
Sub CountBlankRows()
Dim rng As Range, rng1 As Range, rng2 As Range
Set rng = Range("A1:Z100")
Set rng1 = Intersect(rng, ActiveSheet.UsedRange)
If Not rng1 Is Nothing Then
NonUsedRows = rng.Rows.Count - rng1.Rows.Count
On Error Resume Next
Set rng2 = rng1.SpecialCells(xlConstants).EntireRow
On Error GoTo 0
If rng2 Is Nothing Then
nrow = 0
Else
nrow = Intersect(rng2, Columns(1)).Count
End If
MsgBox NonUsedRows + _
(rng1.Rows.Count - nrow)
Else
MsgBox rng.Rows.Count
End If

End Sub

 

[Tom Ogilvy]

I interpreted your question to be what you wrote - blank rows. If you
actually meant blank cells, then mine will not give that.