Macro to stop running at last row

**staples** · 08-10-2009, 06:23 AM

Hi there

I have a spreadsheet containing a macro to automatically sum values from week to week and display the max/min and average. This is almost fully working. The problem I have is the macro copies the sum formula down the page and doesn't stop at the last row. It always adds one or two extra rows.

The formula should stop at row 77 however this does not happen and therefore the sum keeps going. Which in turn, distorts the max/min figures.

I am really stumped how to sort this out
Thanks

**Palmetto** · 08-10-2009, 09:29 AM

Firstly, there are no macros in your workbook, just formulas.

Second - NEVER reference an entire column in a formula.

Your problem is easily solved by using a Dynamic Named Range as the range reference in the formulas. Dynamic Named Range automatically expand/contract as data are added/deleted in the range.

In your workbook, the following dynamic named ranges were created and used:
Holdings: =OFFSET('Report 1'!$C$1,0,0,COUNTA('Report 1'!$C:$C),1)
WeeklySum: =OFFSET(Holdings,0,1)

Amended formulas
Max: =MAX(WeeklySum)
Min: =MIN(WeeklySum)
Average: =AVERAGE(Holdings)

TIP: never change the horizontal alignment of a data cell. By default Excel right aligns all numeric values and left-aligns all text values. By using these default horizonal alignments you can tell at a glance if cell contents are numeric and text.