Macro help required

Hi friends,
Is there any way to count how many blocks of data are there on a sheet.
You can call it as tables or blocks. If there are many tables (Data
blocks) are there on a single sheet is there any way to get a count of
number of blocks?. I think Areas.count will work only with selection.
Regards,
Diva

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
'-----------------
"Diva" <divakarprabhu2000@yahoo.com> a écrit dans le message de news:
1147158892.052728.20910@g10g2000cwb.googlegroups.com...
> Hi friends,
> Is there any way to count how many blocks of data are there on a sheet.
> You can call it as tables or blocks. If there are many tables (Data
> blocks) are there on a single sheet is there any way to get a count of
> number of blocks?. I think Areas.count will work only with selection.
> Regards,
> Diva
>

Hi Ardus,
It works, Thank you very much. I tested it in some cases it works
wonderful.
Regards,
Diva

Thanks for the feedback!

Cheers,
--
AP

"Diva" <divakarprabhu2000@yahoo.com> a écrit dans le message de news:
1147171524.978443.46210@v46g2000cwv.googlegroups.com...
> Hi Ardus,
> It works, Thank you very much. I tested it in some cases it works
> wonderful.
> Regards,
> Diva
>