Countif

[Brian]

How do I modify this part of my macro so that I can use it
on a file that ends on row 20 instead of 13? I want to be
able to use it on files that end on various rows. TIA

ActiveCell.FormulaR1C1 = "=COUNTIF(R[-13]c:R[-1]C,0)"

 

[GB]

How do I modify this part of my macro so that I can use it
on a file that ends on row 20 instead of 13? I want to be
able to use it on files that end on various rows. TIA

ActiveCell.FormulaR1C1 = "=COUNTIF(R[-13]c:R[-1]C,0)"

Fairly obviously you can do this manually
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-20]c:R[-1]C,0)"

Or to do this programmatically:

MyRows=20
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-" & MyRows & "]c:R[-1]C,0)"

You may also find this helpful:
activesheet.usedrange.rows.count
- it gives no of rows in use on current worksheet


Geoff