dynamic ranges getting invalidated
 

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

dynamic ranges getting invalidated
 

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

--
Message posted from http://www.ExcelForum.com

dynamic ranges getting invalidated
 

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)

In article ,
"rtos" wrote:

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