Using VBA to expand a formula each time it runs

[cimajc17]

HI,
I am trying to expand a formula each time this macro runs but i am having trouble extending the range of my function as more data is added,

The formula is this
Range ("E2').select
ActiveCell.FormulaR1C1 = "=COUNTIF(R2C2:R3549C2,1)"

But i want the formula to work using the last filled row in column B as i add more data each time i run the macro

I have tried this but it does not work

startRow = 1
col = 1
lastRowHistogram2 = Findlastrow(col)

Range("E2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R2C2:RlastRowHistogram2C2,1)"
with the function find last row as follows
Function Findlastrow(col) As Long
Findlastrow = Cells(Rows.Count, col).End(xlUp).row
End Function

 

[revcanon]

Try this in your macro:

Define the last row first and then have the formula reflect that last row

LastRow = Range("B" & Rows.Count).End(xlUp).Row
Range("E2").Formula = "=Countif(B2:B" & LastRow & ",1)"

Test in a copy!