Macro help required

[Diva]

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

 

[Ardus Petus]

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]

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

 

[Ardus Petus]

Thanks for the feedback!

Cheers,