Calculate average of both the columns and rows using VBA

Question

I have a dataset that looks like the following:

Column A always contains ascending numbers (x-as in graphs for the time[s]).

The following columns contain data. Both the amount of rows and columns differs everytime dynamically. I want to do two things in Excel using vba:

1. Calculate the average of each row and past it in the lastCol + 1 (GREEN)
2. Calculate the average of each column and past in in the row áfter the lastRow of column A.

I am a bit struggling with the code (source). To make it work, but also to find the most efficient way.

'Option Explicit

Public Sub ExtractInformation()
      
      'Calculate averages of columns
      Dim lastCol As Long, lastRow As Long, m As Long, n As Long
      Dim rng As Range
        
      'Find the last column.
      'Assumes the relevant column is the last one with data in row 5.
      'With Sheets("Graphs")
      lastCol = Sheets("Graphs").Cells(1, Columns.Count).End(xlToLeft).Column
      lastRow = Sheets("Graphs").Cells(Rows.Count, "A").End(xlUp).Row
      'End With
           
      'Iterate the columns from 1 (ie "A") to the last.
      For m = 1 To lastCol
          With Sheets("Graphs")
              'Define the data range for this column.
              'Assumes last cell from bottom of sheet is the end of data.
              Set rng = .Range(.Cells(1, m), .Cells(.Rows.Count, m).End(xlUp))
              'Print the averages on row 125
              .Cells(126, m) = WorksheetFunction.Average(rng) 'Print the averages on row 125
          End With
      Next
      
  '    For n = 1 To lastRow
  '        With Sheets("Graphs")
  '            'Define the data range for this column.
  '            'Assumes last cell from bottom of sheet is the end of data.
  ''             Set rng = .Range(.Cells(n, 1), .Cells(n, .Columns.Count).End(xlLeft))
    '          'Print the averages on row 125
    '          .Cells(128, n) = WorksheetFunction.Average(rng) 'Print the averages on row 125
    '      End With
    '  Next

End Sub

This code works somewhat, except for the part with the comments (uncommenting gives "Error 1004". Also, the calculated averages do not match the averages that I calculated manually. I assume that has something to do with the different amount of rows for each column.

Answer 1

The reason for your 1004 is easy to explain: When you look for the last column by using End, you need to use xlToLeft, not xlLeft. The constant xlLeft has a different meaning (it is used for text alignment).

I assume that you want to calculate the average of the rows/columns by ignoring the empty cells. Easiest ways is to use AverageIf:
AverageIf(rng, "<>") will ignore all cells without values.
AverageIf(rng, "<>0") will ignore all cells without values or with value 0

Equipped with that, you can cleanup your code.

Note that I have created a small function getAgv: It has an error handler to ignore runtime errors if the range doesn't contain any values (like your row 25), and it centralizes the logic of calculation so that you don't have to repeat the formula.

Also, I assume that you don't want to add the values of column A into your average calculation, so I excluded them.

Public Sub ExtractInformation()
    Dim lastCol As Long, lastRow As Long, row As Long, col As Long
    Dim rng As Range
    
    'Find the last column.
    'Assumes the relevant column is the last one with data in row 5.
    With Sheets("Graphs")
        lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        lastRow = .Cells(Rows.Count, "A").End(xlUp).row
    
        'Iterate the columns from 2 (ie "B") to the last.
        For col = 2 To lastCol
            Set rng = .Range(.Cells(1, col), .Cells(lastRow, col))
            .Cells(lastRow+1, col) = getAvg(rng)
        Next
              
        'Iterate the rows from 1 last row
        For row = 1 To lastRow
            Set rng = .Range(.Cells(row, 2), .Cells(row, lastCol))
            .Cells(row, lastCol + 1) = getAvg(rng)
        Next
    End With
End Sub

Public Function getAvg(rng As Range) As Variant
    On Error Resume Next
    getAvg = WorksheetFunction.AverageIf(rng, "<>")
    On Error GoTo 0
End Function