# Count Cells Between Two Values

1. ## Count Cells Between Two Values

I have a column (AS) with several plus signs (+) located down it. Between each plus sign are blank cells. The number of blank cells are always random. For example, the column may look like this:

ROW 14 + LOOP 1 start
ROW 15 BLANK
ROW 16 BLANK
ROW 17 BLANK
ROW 18 + LOOP 2 start
ROW 19 BLANK
ROW 20 BLANK
ROW 21 + LOOP 3 start
ROW 22 BLANK
ROW 23 BLANK
ROW 24 BLANK
ROW 25 BLANK
ROW 26 + LOOP 4 start…etc.

I then created two variables: fr, and lr (first row, last row). With these two variables I then tried to create a loop statement that sets y = to the difference and then changes based on the next loop.

For example, for the first loop fr would be equal to row 14 and lr would be equal to row 18. Therefore, (lr -fr) – 1 = 3 which is the number of blank cells I’m looking for. Then it would go onto the second loop where fr would now equal row 18 and lr would equal row 21. Therefore, (lr - fr)-1 = 2. So on and so forth until reaching the last row.

If I knew the difference for each loop I think I could make the rest of my code work. Any help would be appreciated. Thank you.

2. ## Re: Count Cells Between Two Values

I have tested this on your data. You were a bit vague on your overall problem so I don't know what you want to do with any of this information as you loop through it.
``Please Login or Register  to view this content.``

3. ## Re: Count Cells Between Two Values

Like Jeff alluded to, it makes things easier if we know what needs to be done with the empty cells.
In this code, they are selected and counted, as you asked for.
Slightly shorter code.
``Please Login or Register  to view this content.``

4. ## Re: Count Cells Between Two Values

Originally Posted by Neuk
I have a column (AS) with several plus signs (+) located down it. Between each plus sign are blank cells. The number of blank cells are always random
Do you just want to get blanks?
``Please Login or Register  to view this content.``

5. ## Re: Count Cells Between Two Values

Originally Posted by 6StringJazzer
I have tested this on your data. You were a bit vague on your overall problem so I don't know what you want to do with any of this information as you loop through it.
``Please Login or Register  to view this content.``
6StringJazzer,

You hit on the head. This is exactly what I was looking for. I just needed something to define the x and y-axis for me, which you definitely did. Here's your code with a little manipulation for my purposes:

``Please Login or Register  to view this content.``
With the x and y axis now defined I can total several columns on the x axis, using the y axis to determine how far down I need the numbers totaled. Thank you very much!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1