Count rows between two areas in a collection/selection of multiple areas

[JTwrk]

I am trying to count the number of rows between areas in a collection (not sure if considered to just be a selection or a collection)

The code selects only the visible cells in the range specified. Each block of contiguous cells then makes up 1 area in the collection of areas.

I want to find the number of rows between each of these areas. (I need it for some other code i'm running not relevant to this)

Is it possible to work with the areas in my collection outside of the For Each loop. Like reference the area index somehow IE: could I find the last row of area 1 and the first row of area 2 and then take the difference between the two to get my count of rows between the two areas or do i individually have to loop through each area

something like this

area1lastrow = area1.row + (area1.rows.count - 1)
area2row = area2.row
rowbetween = area2row - area1lastrow - 1
run my other code

or is it possible to select the non visible cells in the range (like a non visible equivalent of the specialcells(xlcelltypevisible) command or some other method) and create a collection of areas made up of each block of non visible cells so I can then count the rows in each of those areas

something like this

For Each nonVisibleAr in activecell.Range("A1:A30").SpecialCells(xlcelltypenonvisible).areas
        rowbetween = nonVisibleAr.rows.count
        run my other code
Next

The current method i am using is just running a loop that checks if each cell is hidden and if hidden increments the rowsbetween up to get the rows between areas. It works but i'm trying to avoid loops if possible and wanted to see if there was some cleaner method to do this.

Here is that code for reference or see the attached file (module 1):

Option Explicit
Option Base 0
Sub CheckAreas()
Dim DataBlock As Range
Dim rowoffset As Long
Dim areacount As Long
Dim areaschecked As Long
Dim rowsbetween As Long

Range("A2").Select

areacount = ActiveCell.Range("A1:A30").SpecialCells(xlCellTypeVisible).areas.Count
For Each DataBlock In ActiveCell.Range("A1:A30").SpecialCells(xlCellTypeVisible).areas
    areaschecked = areaschecked + 1
    rowoffset = DataBlock.Rows.Count
    rowsbetween = 0
    If areaschecked < areacount Then
        Do While DataBlock.Offset(rowoffset, 0).Range("A1").EntireRow.Hidden = True
            rowoffset = rowoffset + 1
            rowsbetween = rowsbetween + 1
        Loop
    End If
    
    MsgBox "DataBlock Address " & DataBlock.Address(RowAbsolute:=False, columnAbsolute:=False) _
    & " area " & areaschecked & " has " & DataBlock.Rows.Count & " Rows" & vbCrLf _
    & "And There are " & rowsbetween & " Rows between area " & areaschecked & " and the start of the next area"
    
Next

End Sub

[JTwrk]

bump for posterity

[Andy Pope]

do you use the Areas object?

Set rngData = ActiveCell.Range("A1:A30").SpecialCells(xlCellTypeVisible)

For lngArea = 1 To rngData.areas.Count - 1

    rowsbetween = rngData.areas(lngArea + 1).Rows(1).Row - (rngData.areas(lngArea).Row + rngData.areas(lngArea).Rows.Count)
    MsgBox "DataBlock Address " & rngData.areas(lngArea).Address(RowAbsolute:=False, columnAbsolute:=False) _
    & " area " & lngArea & " has " & rngData.areas(lngArea).Rows.Count & " Rows" & vbCrLf _
    & "And There are " & rowsbetween & " Rows between area " & lngArea & " and the start of the next area"
    
Next

[JTwrk]

sweet that works however can you clarify what you mean by "do you use the areas object"?

I thought I was using the areas object in my last code example above though i didn't set the range to a variable, i just added .areas after my range and am using the for each loop to loop through each area

Two other questions:

is what you did above, calling or referencing the different areas to find out the number or row of each possible inside of a For Each loop or would that only be possible if you nested your for next loop above in the For Each (which would be rather pointless I assume). Just curious

also why do you specify Rows(1). When referencing each area for a row it always gives the first row by default doesn't it? Or are there cases where it may not and so that is just good coding practice to avoid that risk? or just some other reason?

thanks

[Andy Pope]

Should have said, "do you mean the Areas object?"

If you use a FOR EACH loop you will only have reference to the current area. You will not have information about the next area in order to calculate gap inbetween.

Just the way I code. I tend not to use default properties/objects, instead I specify what I want to deal with.

[JTwrk]

I didn't know you could do the rngdata(#index) to access information about the different areas.

that got me thinking and i was tinkering with your code and found that you can actually reference the next area in a for each loop.

originally i was trying to do

 Datablock.areas(# index)...

which didn't work because each datablock only encompasses the current area it's on (assuming).

i then tried the

rngdata.areas(#index)...

from your code in the for each loop and it worked. I just added a counter to the code to increment each time it goes through

Here is the code i put together to test it:

Sub CheckAreas()
Dim DataBlock As Range, rngData As Range
Dim rowoffset As Long, areaschecked As Long, rowsbetween As Long
Dim CurArea As Long, NextArea As Long, etad As Long, otad As Long, rtad As Long, qtad As Long

Range("A2").Select

Set rngData = ActiveCell.Range("A1:A30").SpecialCells(xlCellTypeVisible)

For Each DataBlock In rngData.areas
    
    '''Find rows in current area and end sub if current area not factorable by 6 (if not cbool (mod6)
    etad = DataBlock.Rows.Count 'test line not really needed
    
    areaschecked = areaschecked + 1
    rowsbetween = 0
    
    '''Find rows between current area and next area and end sub if current area not factorable by 6 (if not cbool (mod6))
    If areaschecked < rngData.areas.Count Then
        CurArea = CurArea + 1
        NextArea = CurArea + 1
        '''Test'''
        otad = rngData.areas(CurArea).Rows(1).Row
        rtad = rngData.areas(NextArea).Rows(1).Row
        '''End Test'''
        
        rowsbetween = rngData.areas(NextArea).Rows(1).Row - (rngData.areas(CurArea).Row + rngData.areas(CurArea).Rows.Count)
    End If
    
    ''Test line''
    qtad = Range(DataBlock.Offset(etad, 0).Range("A1"), "STOPRC").Rows.Count - 1
    
    MsgBox "DataBlock Address " & DataBlock.Address(RowAbsolute:=False, columnAbsolute:=False) _
    & " area " & areaschecked & " has " & DataBlock.Rows.Count & " Rows" & vbCrLf _
    & "And There are " & rowsbetween & " Rows between area " & areaschecked & " and the start of the next area"
    
Next

End Sub

I have no idea if a for each or for next loop is more efficient than one or the other but your code is admittedly much shorter though i'll be getting rid of all the test lines and message boxes so will cut down on it by a bunch. I wouldn't have known to try that though if it weren't for your code so i'll mark this as solved.

thanks

[Andy Pope]

Just to be clear using the object rngData in the FOR EACH loop where DataBlock is the loops reference object is not really doing it within a FOR EACH loop. That would only be correct if you used the DataBlock obejct. The problem is DataBlock refers to a single contiguous range.

[JTwrk]

i'm not sure i understand

For each area
........it checks the number or rows in the current area
........then checks the number of rows between the current area and the next area by getting the info on the other areas in the collection of areas by use of the rngData.area(index#)
Loops to check next area

Now that i typed that out i'm thinking this:

by "not really doing it within a FOR EACH loop" do you mean that because once the rngData object has been created the areas collection exists and can be referenced anywhere in the module whether inside a loop or not. So it doesn't have to be in a loop to reference it (just tried it and i guess it works). So really i'm just using my DataBlock range as my counter then i guess, which is kind of the same as using the for next loop i suppose.

So that leads me to this question:

When would you use the for each loop rather than a for next loop, it seems like you can do the same operations with both with either one you can reference the current area. Does one loop open up different code options or something. are there any problems i introduce by doing it the way i have?

[Andy Pope]

you would use the for each if you were only interested in the range returned.
But for your problem in are interested in the ranges between the ranges returned. And in order to determine those you would need to store information in other variables as you iterate through the loop.

So it's better to use a FOR NEXT loop where you can control the start and end index values. Notice my code does not loop for the last area in the range.
The you can access any of the Areas objects of the rngData object

[JTwrk]

i see so i avoid unnecessary variables and can avoid error more easily with the for next by explicitly controlling the end while with the for each i have to put in counters to do the same.

If i try and check the last area i get an error with the for next but that's because there is no next area for the rowsbetween. So i'll just add a if statement where it checks the current area for each loop and only checks the rows between on loops 1 - 2nd to last area and that should work and be shorter.

thanks