VBA : Number of Blank Cells in a Range
 

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

Number of Blank Cells in a Range
 

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)

"Sanka" wrote in message
...
How to get the number of Blanks rows in a range in VBA

Number of Blank Cells in a Range
 

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

or
msgbox application.countblank(selection)


--
Regards
Frank Kabel
Frankfurt, Germany


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

Number of Blank Cells in a Range
 

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

--
Regards,
Tom Ogilvy


"Sanka" wrote in message
...
How to get the number of Blanks rows in a range in VBA

Number of Blank Cells in a Range
 

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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy


"Sanka" wrote in message
...
How to get the number of Blanks rows in a range in VBA