Countif

  • Thread starter Thread starter Brian
  • Start date Start date
B

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)"
 
Brian said:
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)"
Click to expand...

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
 
Back
Top