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

**JTwrk** · 08-01-2012, 09:28 AM

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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):

Please Login or Register  to view this content.

**JTwrk** · 08-02-2012, 05:46 AM

bump for posterity

**Andy Pope** · 08-02-2012, 06:21 AM

do you use the Areas object?

Please Login or Register  to view this content.

**JTwrk** · 08-02-2012, 07:40 AM

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** · 08-02-2012, 07:50 AM

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** · 08-02-2012, 08:45 AM

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

Please Login or Register  to view this content.

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

i then tried the

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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** · 08-02-2012, 09:05 AM

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** · 08-02-2012, 10:10 AM

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** · 08-02-2012, 10:20 AM

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** · 08-02-2012, 10:38 AM

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