<div>
route217,<br />
<br />
Have you considered using dynamic named range?<br />
<br />
BTW, in your question there seem to have some cell reference error.<br />
<br />
You said A6 formula is =Row(A$53)-row(A$6)-1<br />
<br />
Then you said<br />
A6 formula is =Max(A6,1)<br />
<br />
Set a named range Called LastRw or something with the following reference<br />
=row(OFFSET($A$1,COUNTA($A<wbr />:$A)-1,0))<wbr /><br />
<br />
This way, the LastRw is always reference to the last cell with data in column A.
</div>


route217,

Have you considered using dynamic named range?

BTW, in your question there seem to have some cell reference error.

You said A6 formula is =Row(A$53)-row(A$6)-1

Then you said
A6 formula is =Max(A6,1)

Set a named range Called LastRw or something with the following reference
=row(OFFSET($A$1,COUNTA($A:$A)-1,0))

This way, the LastRw is always reference to the last cell with data in column A.

<div>
Harry lee<br />
Firstly thanks for the excellent feedback. .do u have any explain wrkbk you could kindly attached, &nbsp;please.
</div>


Harry lee
Firstly thanks for the excellent feedback. .do u have any explain wrkbk you could kindly attached,  please.

Dynamic-Named-Range-Sample.xlsx

<div>
For more guideline, you can take a look at this microsoft post.<br />
<br />
<a href="http://support.microsoft.com/kb/830287" rel="ugc">Dynamic Named Range</a>
</div>


For more guideline, you can take a look at this microsoft post.

Dynamic Named Range [http://support.microsoft.com/kb/830287]

<div>
<div class="content wysiwyg-content">
Hi Experts excel 2007<br />
<br />
I am using the following formula in cell a6 =row(a$53)-row(a $6)-1<br />
<br />
In a6 I have formula =max (a6,1)<br />
<br />
In colc. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Cold. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br />
Sheet1! C6:c53. &nbsp; &nbsp; &nbsp; Sheet1! D6:d53<br />
<br />
I want the value of A53 (formula in a6) to increase and decrease as row are add or deleted from table.<br />
Assume table A6:d53
</div>
</div>


Hi Experts excel 2007

I am using the following formula in cell a6 =row(a$53)-row(a $6)-1

In a6 I have formula =max (a6,1)

In colc.                     Cold.                      
Sheet1! C6:c53.       Sheet1! D6:d53

I want the value of A53 (formula in a6) to increase and decrease as row are add or deleted from table.
Assume table A6:d53

amend current formula to amend table row height

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.