(trying again...) code stops executing

[Mark Kubicki]

I'm monitoring changes that happen within different ranges of cells
(A1:A10, A1:Z1, A1:Z10, etc...)

rows may be inserted or deleted above the "last" row being monitored, so,
the lower limit rows may change.
(a range previously defined as A1:A10 becomes A1:A9, or A1:A22 or
whatever...)

to keep the range being monitored current, I've defined the last row as name
(TotalCellRef), and define the monitored range relative to the name's
position:
Range("B9:B" & CStr(ActiveSheet.Range("TotalCellRef").Row) - 1)

this works well, EXCEPT after I've inserted or deleted row(s), 'calculation'
turns to manual, and the VBA code stops executing (doesn't return an error,
just does nothing).

if after changing the row count, I save and reopen the doc/XL, I am then
referred to the correct row (which is different from when I previously
opened the doc...)

???

 

[Don Guillett]

Why not just use a defined name that is self adjusting.
On the page where you want this>
insert>name>define>type in a name of your choice like myrng
in the refers to box type in

=offset($b$9,0,0,counta($b:$b)-8,1)

You may want to adjust the -8 for what's in b1:b8
test by typing in the name in the name box