finding average of consecutive columns RRS feed

  • 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)
      MsgBox Join(Average, vbCrLf)
      Exit Sub
      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 and post the download link here.

    A macro to anonymize data in selected cells can be downloaded here:


    Saturday, November 26, 2016 7:20 AM
  • Thank you very much. I am uploading a sample. The link is!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.


    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
        Average(i) = WorksheetFunction.Average(This)
      MsgBox Join(Average, vbCrLf)
      Exit Sub
      Average(i) = "(none)"
      Resume Next
      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
      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.



    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

    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