count exact range

Question

Is there a formula or method of keeping an exact amount of rows (i.e. 100 rows always) and counting inside said rows - regardless of inserts or deletes? I've tried absolute references, but the inserts and deletes affect it.

The only method that I have now is to rewrite the formulas after all the inserts and deletes have been completed.

Sub ExactRowsCount()
'
' ExactRowsCount Macro
'
Range("H1").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[4]C:R[104]C)"
Range("H2").Select

End Sub

Do you mean to always have the `COUNTA` function count in the same number of rows regardless of whether rows/cells were inserted or deleted? — ikh, Mar 13 '13 at 17:52

score 3 · Accepted Answer · answered Mar 13 '13 at 18:07

I would use OFFSET:

=COUNTA(OFFSET(A1,4,0,100,1))

Where A1 is the cell where you want the count to end up. This formula may be particularly convenient if you want the column height to depend on another cell; for example, if you want to keep the column height in cell A2 instead of using a constant number 100, you could do:

=COUNTA(OFFSET(H1, 4, 0, A2, 1))

score 2 · Answer 2 · answered Mar 13 '13 at 17:57

2

This will return the count of non-blank cells in A2 to A101 regardless of inserts and deletes:

=COUNTA(INDIRECT("A2:A101"))

answered Mar 13 '13 at 17:57

Doug Glancy

27,214
6
67
115

Simple answer, but I went with ikh's answer because it is more customizable. Thanks! – Deafdan Mar 13 '13 at 19:13

count exact range

2 Answers2