Excel Macro - Formulas 2

[Yevette]

In my macro I'd like to put a sum formula at the end of the column, but within the column there are blank cells and since the spreadsheet has constantly # of rows there is no static range I can input in the macro to capture all of my data. Is there a macro command to use to capture an "ever changing" range for a formula?

Thanks for your help!

 

[etarhan]

Hi Yvette,

Please read the following excerpt from a help column (I don't remember which): It may be helpful
----
I have an Excel worksheet with data in cells A1:B10 plotted on a chart. Each day I add another row of data. How can I make the chart range grow automatically, so that tomorrow it is charting A1:B11, the following day A1:B12, and so on?

Let's assume you're working in Sheet1 with column headings in row 1 and days in column A. Data relating to each date is in column B. Using Insert | Name | Define, type each name in the Names in workbook area, and then type the corresponding formula in the Refers to area. Click Add and type in:
Days=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)
PlotData=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)

Click on the series in your chart so the =SERIES formula appears in the formula bar. Edit the second and third arguments in the formula, replacing the reference to the column A range with the range name Days and the reference to the column B range with the range name PlotData. The result should be something like this:
=SERIES(Sheet1!$B$1,Book1!Days,Book1!PlotData,1)
Now the chart will automatically grow as you add data.
-----------

Good Luck!