# finding average of consecutive columns

• ### Question

• I have a table 6 rows x 12 columns.

1. I need to find the average of each row on press of a button.
2. The worksheet contains 6 such tables and the workbook contains 8 such sheets. How do I repeat the same formula for all the tables?
Saturday, November 26, 2016 6:25 AM

### All replies

• To 1.

I assume that the current selected cell is inside such a data block.

```Sub Test()
Dim Where As Range, This As Range
Dim Average, i As Long
Set Where = ActiveCell.CurrentRegion
ReDim Average(1 To Where.Rows.Count)
On Error GoTo ErrorHandler
For Each This In Where.Rows
i = i + 1
Average(i) = WorksheetFunction.Average(This)
Next
MsgBox Join(Average, vbCrLf)
Exit Sub

ErrorHandler:
Average(i) = "(error)"
Resume Next
End Sub```

To 2.

Depends on the layout of the sheets and the tables if it is possible to determine where the data is located

If you need further help please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

A macro to anonymize data in selected cells can be downloaded here:
https://dl.dropboxusercontent.com/u/35239054/modAnonymize.bas

Andreas.

Saturday, November 26, 2016 7:20 AM
• Thank you very much. I am uploading a sample. The link is

https://1drv.ms/x/s!AhnIk9vtL9OSh0P4JgePy8a89NPs

Saturday, November 26, 2016 9:24 AM
• To get the average in each table you have to search for the headers in each table.
So if the header in all tables are the same it is possible.

A routine for one table is below, to access all tables in the file search for "PO1" and apply the code below on that cells.

Andreas.

```Sub Test()
Dim FCH As Range, LCH As Range, FRH As Range, LRH As Range
Dim Where As Range, This As Range
Dim Average, i As Long

On Error GoTo ErrorHandler
'Get the current region
Set Where = ActiveCell.CurrentRegion
'Find the headers
Set FCH = Where.Find("PO1", LookIn:=xlValues, LookAt:=xlWhole)
Set LCH = Where.Find("PSO2", LookIn:=xlValues, LookAt:=xlWhole)
Set FRH = Where.Find("CO1", LookIn:=xlValues, LookAt:=xlWhole)
Set LRH = Where.Find("CO6", LookIn:=xlValues, LookAt:=xlWhole)
'Get interection => the data
Set Where = Intersect(Range(FCH, LCH).EntireColumn, Range(FRH, LRH).EntireRow)
'Build the average in of each row
ReDim Average(1 To Where.Rows.Count)
i = 0
On Error GoTo AverageError
For Each This In Where.Rows
i = i + 1
This.Select
Average(i) = WorksheetFunction.Average(This)
Next
MsgBox Join(Average, vbCrLf)
Exit Sub

AverageError:
Average(i) = "(none)"
Resume Next

ErrorHandler:
Where.Select
If Err.Source = "" Then Err.Source = Application.Name
Debug.Print "Source     : " & Err.Source
Debug.Print "Error      : " & Err.Number
Debug.Print "Description: " & Err.Description
If MsgBox("Error " & Err.Number & ": " & vbNewLine & vbNewLine & _
Err.Description & vbNewLine & vbNewLine & _
"Enter debug mode?", vbOKCancel + vbDefaultButton2, Err.Source) = vbOK Then
Stop 'Press F8 twice
Resume
End If
End Sub
```

Sunday, November 27, 2016 10:30 AM
• Hi Vullas,

as you had mentioned that you have a 6 tables on same sheet.

and you want to find average of some columns in each table and display the average in totals row of table.

here below is an example code you can try to understand and based on this code you can try to develop your own code to loop through all the tables in the sheet to display the average value.

```Sub ChangeAllColumnTotals()

Dim tbl As ListObject
Dim CalcType As Integer
Dim x As Long

Set tbl = ActiveSheet.ListObjects("Table1")

'What calculation should the Totals Row Have?
CalcType = 1 'or: xlTotalsCalculationSum

'Loop Through All Table Columns
For x = 1 To tbl.ListColumns.Count
tbl.ListColumns(x).TotalsCalculation = CalcType
Next x

'___________________________________________
'Members of xlTotalsCalculation
'Enum       Calculation
' 0           None
' 1           Sum
' 2           Average
' 3           Count
' 4           Count Numbers
' 5           Min
' 6           Max
' 7           Std Deviation
' 8           Var
' 9           Custom
'___________________________________________

End Sub```

please refer the link mentioned below to get detailed idea about how this code works.

change all table column's total row calculation

Note: you have to develop your code. this code will just give you an idea.

Regards

Deepak

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Monday, November 28, 2016 3:14 AM
• Thanks Very much Deepak. I find it difficult to understand complex code I have not written myself. Totally new to this. I found your site helpful. I will try to develop my code and get back if I am stuck somewhere.
Monday, November 28, 2016 7:48 AM
• I found your site helpful. I will try to develop my code and get back if I am stuck somewhere.
Funny, your file doesn't contain any listobject...
Monday, November 28, 2016 8:15 AM