Writing Formulas to Include All Rows in Worksheet

[Fao, Sean]

What is the proper technique for writing a formula that includes all
rows in a worksheet? The number of rows in the worksheet change
frequently. So, for example, if I wanted to sum all of the rows from A1
through A1000 today, but A1 through A1100 tomorrow (because of new rows
added), I would have to update my formula to include the new rows. I
could write my formulas to include rows with no data in them (e.g.,
=SUM(A1:A1048576)), but is there a better way to write a formula that
automatically "expands" as new rows are added?

Thank you in advance,

 

[Jim Cone]

=SUM(A:A)
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Fao, Sean"
<[email protected]>
wrote in message
What is the proper technique for writing a formula that includes all
rows in a worksheet? The number of rows in the worksheet change
frequently. So, for example, if I wanted to sum all of the rows from A1
through A1000 today, but A1 through A1100 tomorrow (because of new rows
added), I would have to update my formula to include the new rows. I
could write my formulas to include rows with no data in them (e.g.,
=SUM(A1:A1048576)), but is there a better way to write a formula that
automatically "expands" as new rows are added?
Thank you in advance,

 

[james.igoe]

Also, it depends on what you need to do, so for some advanced
techniques:

1. Use the UsedRange property to get the object for an entire range,
from which you can set the value to an array, count the number of row
or columns, or simply use as an object.

2. Use an equation to define a range of values, such that if the
number of rows increases, the size of the range increases, and that
expands the columns included as well:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

3. To do something similar by row, use the row number, as in the
COUNTA(Data!$1:$1) to count the number of contiguous columns in the
first row.

James Igoe

http://vba.wikidot.com/
http://code.comparative-advantage.com/

 

[Fao, Sean]

Also, it depends on what you need to do, so for some advanced
techniques:

1. Use the UsedRange property to get the object for an entire range,
from which you can set the value to an array, count the number of row
or columns, or simply use as an object.

2. Use an equation to define a range of values, such that if the
number of rows increases, the size of the range increases, and that
expands the columns included as well:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

3. To do something similar by row, use the row number, as in the
COUNTA(Data!$1:$1) to count the number of contiguous columns in the
first row.

Thank you very much for your reply. This is exactly what I was looking for.

 

[Harlan Grove]

2. Use an equation to define a range of values, such that if the
number of rows increases, the size of the range increases, and that
expands the columns included as well:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

....

And if there are any blanks in either col A or row 1, this approach
will fail to include the entire used range. The other suggestion,
using A:A will ALWAYS be UNAFFECTED by row insertions or deletions,
and much, much better, it doesn't involve an unnecessary volatile
function call.