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

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)

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

perhaps

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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