VBA code to average last 6 columns in a row

[J1149]

I have a spreadsheet with 52 columns (weekly sales to date)of data. I need to average the sales for last six columns with data in a row. Anyone who can help with this?

Thanks in advance

 

[SydneyGeek]

You could use a formula for this.
As an example I have headings down column A, and data from B to P, with some of the end columns cleared out. Therefore each row has data in every cell to the last one, no gaps, and then blank cells.
The formula is in T2. filled down.

First: create a dynamic named range.
Select B2:G2 and create a range called LastSix.
Go to the Name Manager (Insert > Name > Define for 2003-, or Formulas > Name Manager for 2007+)
Select LastSix and replace the reference with this formula:

=OFFSET(Sheet1!$B14,0,COUNTA(Sheet1!$B14:$P14)-6,1,6)

OK to accept the defnition, then close the dialog.

In T2, the formula is =AVERAGE(LastSix)

Note: Adjust references to handle the 52 columns of data.

This part of the formula is where you define the start point of hte last 6 columns. Adjust to suit:
COUNTA(Sheet1!$B14:$P14)-6

Denis