VBA - entering a formula in excel

[Marie]

I want to be able to enter a formula into the spreadsheet which counts the
number of entries in a range.
However the range can be a variable number of rows.
It will however always refer to the row which is end(xlup) from the cell
containing the formula (ie my start point - the activecell) and it will
always anchoring from this pount to end(xlup) twice then down one cell. So
basically i am wanting to enter a number of formulas in various parts of the
spreadsheet which are never in the same location
-counta(End(xlup)End(xlup)end(xlup)(down)) are my key strokes - but can not
see how to replicate this in VBA

 

[Bob Phillips]

Dim rng As Range

Set rng = Range(ActiveCell, ActiveCell.End(xlUp).End(xlUp).Offset(1, 0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Don Guillett]

perhaps

lastrow=cells(rows.count,"a").end(xlup).row
or
lastrow=cells(activecell.row,activecell.column).end(xlup).row