Defined Range Problem

U

unplugs

I define a range.

After that when I delete the first row of the range(either using VBA o
delete directly from spreadsheet) the range that I define had lost an
changed.

How to solve this problem.. ?

I had waiting for more then a weeks for this... Hope any of you tha
encounter this problem before can guide me ..... Thanks a lot.
 
N

Nick Hodge

It depends how complicated the range is. If it is lots of non-contiguous
cells then it would be more difficult but if it is all the cells to the
bottom of a contiguous range and a few columns you could reset this with
code and allocate it a button or shortcut key

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
U

unplugs

The problem is....

I define the range as :
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),10)

After I delete the data in the first row of this range, it becomes lik
this:
=OFFSET(Sheet1!#REF!,0,0,COUNTA(Sheet1!$A:$A),10)

How to solve this problem... ? It really make me headche... :<

Nick Hodge or any other fren can guide me on this..
 
G

George Nicholson

Workaround: Don't delete the first row of the range. Copy the contents of
the 2nd row to the first row and then delete the 2nd row (which is now the
first row).
 
U

unplugs

Thanks George Nicholson!!! Thanks for ur suggestion! I'm already figur
out!!! :)

my work on this:
1) Copy the second row
2) paste in first row
3) delete the second row

Hihi... THanks... I love this Forum.
:
 
Top