Excel vba - calculate average of data with a dynamic number of columns

Question

I'm new to excel VBA, I want to calculate the average of each column that has data

But the number of columns can change depending on how many columns worth of data the users copy and pastes.

Here's what I have so far:

Dim lastcol As Long
Dim i As Integer

lastcol = Range("B5", Range("B5").End(xlToLeft)).Columns.Count + 1

For i = 0 To lastcol

Range(ActiveCell, ActiveCell.Offset(0, i)).Value = 
Application.WorksheetFunction.Average(Range(Range("B5").Offset(0, i), 
Range("B5").Offset(0, i).End(xlDown)))

Next i

But this seems to calculate the average of only the data in column B. I want to calculate average of each column that has data and put that value into first row without any data.

Ambie Ambie · Accepted Answer · 2019-06-10T03:46:08

For someone new to VBA, this is a good effort, and your code just needs a couple of tweaks.

Firstly, the line:

lastcol = Range("B5", Range("B5").End(xlToLeft)).Columns.Count + 1

will basically find the left end of the region of Range "B5" - most likely "A5"- so lastcol will always equal 3.

Secondly, the line:

Range(ActiveCell, ActiveCell.Offset(0, i)).Value = ...

defines the range beginning at the ActiveCell and ending at the offset value from that cell. In other words, you'll have a number of cells within the range depending on the value of i but the ActiveCell will always be the first cell in the range. Given that your output value is a double and not an array, your code will write that value to every cell in the range. So your previous calculations are always being overwritten.

A point of note also is that you should always qualify your ranges with the sheet object. In your code there is a risk of the wrong worksheet being processed. I'd also steer away from using the ActiveCell property, as one wrong selection and the whole sheet can be messed up.

It's worth stepping through your code with F8, as you can see the values that are assigned to the variables. I also like to select my ranges during testing in order to see what cells are being identified. Perhaps you could adopt these practices in future development.

All in all, then, your code could look something like this:

Dim lastCol As Long, i As Long
Dim rng As Range

'Find the last column.
'Assumes the relevant column is the last one with data in row 5.
With Sheet1
    lastCol = .Cells(5, .Columns.Count).End(xlToLeft).Column
End With

'Iterate the columns from 1 (ie "A") to the last.
For i = 1 To lastCol
    With Sheet1
        'Define the data range for this column.
        'Assumes last cell from bottom of sheet is the end of data.
        Set rng = .Range(.Cells(5, i), .Cells(.Rows.Count, i).End(xlUp))
        'Write the average to the cell above.
        .Cells(4, i) = WorksheetFunction.Average(rng)
    End With
Next

Excel vba - calculate average of data with a dynamic number of columns

2 Answers