Count Rows based on a Find...

[brans]

I am writing code to format a spreadsheet based on subtotals. The problem is that I need to allow the user a variable number of rows based on where their total is. (i.e. They add and delete rows often)

I want to count the number of rows above the word Total.
I get stuck on actually counting the number of rows.

Range("D1&quot.Select
Cells.Find(What:="total", After:=ActiveCell,_ LookIn:=xlFormulas, LookAt _:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _False).Activate


Any help would be greatly appreciated

 

[JVFriederick]

Just a thought . . .

Instead of looking for Total, why not assume it's there, and subtract 1 from the count. Then, based on the row where your data starts, you can deduce the row location of Total. Format your stuff from knowing the start and end points.

P.S. I prefer to put Totals at the top of data ranges so they always stay on the same row, and use the filter feature to generate subtotals without formulas within the data. However, your mileage may vary.

 

[mymou]

ActiveCell.Row

 

[brans]

Thanks for the help!

JVFriederick, I'll definitely keep that in mind.


The activecell.row worked, THANKS! I had tried all different versions of that except that one, DOH.