Looping Through Each Region on Sheet


 
Somewhere i found this interesting question . How to loop through Each Regions on a worksheet
 
this subroutine can loop every regions on worksheets , i was also thinking for a better way (if possible please post here)
 
Sub AllCurrentRegions()
 
    Dim rngUsedRange        As Range
    Dim rngCurrentRegion    As Range
    Dim rngCell             As Range
    Dim VarArr
    Set rngUsedRange = Sheet1.UsedRange.SpecialCells(xlCellTypeConstants)
    Set rngCurrentRegion = rngUsedRange.Cells(1).CurrentRegion
    
    Call DoSomethingWithCurrentRegion(rngCurrentRegion)
    For Each rngCell In rngUsedRange
        If Intersect(rngCurrentRegion, rngCell) Is Nothing Then
            Debug.Print rngCell.CurrentRegion.Address
            Call DoSomethingWithCurrentRegion(rngCurrentRegion)
            Set rngCurrentRegion = rngCell.CurrentRegion
        End If
    Next
    
End Sub
 
Function DoSomethingWithCurrentRegion(CurrentRegion As Range)
    ‘Your Code Goes Here
End Function
 
 
Thanks for reading.
Rajan_Verma

3 Comments Add yours

  1. Betty says:

    That is very fascinating, You’re an overly professional blogger. I have joined your rss feed and look forward to in quest of more of your wonderful post. Additionally, I have shared your site in my social networks

  2. Abhi says:

    Should looping thru the sheets and within it looping thru each section of that sheet help??

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s