dynamic ranges getting invalidated

  • Thread starter Thread starter rtos
  • Start date Start date
R

rtos

Hi,
I developed an excel sheet where I track my stocks, In this sheet I have
a dynamic range defined like this

StockSymbols =OFFSET(Main!$A$10,0,0,COUNTA(Main!$A$10:$A$35),1)

In my vba code I need to delete a row when I no longer own that stock, so I
delete the row in my vba code. If I delete the first row A10 then
this range is getting invalidated and the range becomes

=OFFSET(Main!#REF!,0,0,COUNTA(Main!$A$10:$A$35),1)

I want to know how to fix this problem, are there any better solutions that
I can use.

Thanks
Ravi
 
Hi,

this is untested, and seems a strange workaround but try
StockSymbol
=OFFSET(indirect("Main!$A$10",0,0,COUNTA(indirect("Main!$A$10:$A$35"),1)


HI
 
The easiest way would probably be to use this as the definition of
StockSymbols:

=OFFSET(Main!$A$9,1,0,COUNTA(Main!$A$10:$A$35),1)
 
Back
Top