Macro help required

D

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
 
A

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
'-----------------
 
D

Diva

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