Countif
"Brian" wrote in message
...
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
|