Try this code (not fully tested)
HTH
--
AP
'--------------------
Sub CountBlocks()
Dim rAllBlocks As Range
Dim rCell As Range
Dim strFirstFound As String
Set rCell = Cells.Find( _
what:="*", _
after:=ActiveCell, _
LookIn:=xlFormulas, _
searchorder:=xlByRows)
If rCell Is Nothing Then
MsgBox "Empty Worksheet"
Exit Sub
End If
strFirstFound = rCell.Address
Do
If rAllBlocks Is Nothing Then
Set rAllBlocks = rCell.CurrentRegion
Else
If Intersect(rCell, rAllBlocks) Is Nothing Then
Set rAllBlocks = Union(rAllBlocks, rCell.CurrentRegion)
End If
End If
Set rCell = Cells.FindNext(after:=rCell)
Loop Until rCell.Address = strFirstFound
MsgBox rAllBlocks.Areas.Count
End Sub
'-----------------
> Hi friends,
> Is there any way to count how many blocks of data are there on a sheet.
[quoted text clipped - 3 lines]
> Regards,
> Diva
Diva - 09 May 2006 11:45 GMT
Hi Ardus,
It works, Thank you very much. I tested it in some cases it works
wonderful.
Regards,
Diva
Ardus Petus - 09 May 2006 12:18 GMT
Thanks for the feedback!
Cheers,
--
AP
> Hi Ardus,
> It works, Thank you very much. I tested it in some cases it works
> wonderful.
> Regards,
> Diva