macro or VBA to count the range cells

[chaumay809]

hi
i'm working on a file that it has almost 3600 pages.
now, I have copies all data into excel and need a quick calaluation but it is too much cells to count. so i'm looking for help with the VBA code that would count the cells with the range.

see an example (I made a short example) attach file.

on column A
it begins with
Tree
st1
the range (from cell A3-A13)
it should COUNT it has 11 trees (cells)

then, it would repeat
tree
st2
the range (from cell A16-A20)
it should COUNT IT HAS 5 tress (cells)
see the short summary

so, what I'm looking for is a formula or VBAthe code that could count the range between each tree as the data is not constant as the number of tress are vary for each street.

thanks in advance for the help.

[davesexcel]

There is a formula you can use,

Please Login or Register  to view this content.

You will have to type end at the very last cell though.

end.jpg

Then the formulas can find the row numbers.

4-26-2015 10-26-21 AM.jpg

[JBeaucaire]

1) Insert a blank row at the top

2) Insert a blank column A where we will add an indexing column of numbers.

3) Add the one more Tree a the bottom of the data, then directly below that put the word End for the street name.
http://screencast.com/t/4MY8MCt1Aeo0

4) In A2 enter this formula and copy down all the way :
=IF(OR(B2="Tree",B2= ""), INT(A1)+1, A1+ 0.001)

5) To pull out the street names, put this starting formula in D4 and copy down until street names stop appearing:
=INDEX($B:$B, MATCH(ROW(A1),$A:$A, 0)+1)

6) To pull out the Totals, put this starting formula in F4 and copy down:
=IF(OR(D4="End", D4=""), "", MOD(INDEX(A:A, MATCH(ROW(A1)+0.9999999,A:A, 1)),1)*1000-1)
http://screencast.com/t/ZQq21CdHoJ

[chaumay809]

on the file, column 3 is blank, empty.
I put there is to show what the result it should be.

[JBeaucaire]

Attached is my suggested solution, for reference.

[chaumay809]

it solved part of my problem.
thanks.