Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / May 2006

Tip: Looking for answers? Try searching our database.

Macro help required

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Diva - 09 May 2006 08:14 GMT
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 - 09 May 2006 08:52 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.