Finding average of column values(Program Outcomes) in an excel macro using VBA

 Suppose you have a sheet with following data:

I wanted to generate average of each column to find the learning gap in subjects offered by our university.

Here is the macro that gets triggered upon single click of any cell.

You can cancel inputting the number and say ok.

Otherwise if you want to calculate the columnar average of each POs.. in this macro it takes how many 

rows you need to consider starting from the cell clicked.

It calculates the average and stores it at the end of the sheet after all the rows.

When C3 is clicked this is the state:

after entering 5 the number of rows in this table..

It calculates and stores average in below column:

It has calculated the averages here.

This is the macro code:

' starts off with selection change which triggers when single click on any other cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count = 1 Then

        Dim userInput As String

        Dim clickedCellAddress As String

        

        ' Get the address of the clicked cell

        clickedCellAddress = Target.Address(False, False) ' Use False, False for relative address (e.g., A1)

        

        Call AverageWithInputRows(clickedCellAddress)

    End If

End Sub

' averagewithinputrows is calculating the average after taking the clicked cell address

' as input

Sub AverageWithInputRows(ByVal clickedCellAddress As String)

    Dim sel As Range

    Dim col As Range

    Dim numRows As Variant

    Dim startRow As Long

    Dim targetRange As Range

    Dim colAvg As Double

    Dim lastRow As Long

    ' 1. Explicitly capture the selected range

    Set sel = Range(clickedCellAddress).Resize(1, 12)

    

    ' 2. Get the row from the top-left cell of your selection

    startRow = sel.Cells(1, 1).Row

    ' 3. Prompt for the count

    numRows = InputBox("Rows to average starting from Row " & startRow & ":")

    If Not IsNumeric(numRows) Or numRows = "" Then Exit Sub

    ' 4. Loop through each column in the selection

    For Each col In sel.Columns

        

        ' Define the range for THIS specific column in the loop

        Set targetRange = ActiveSheet.Cells(startRow, col.Column).Resize(CInt(numRows))

        ' 5. Calculate Average

        On Error Resume Next

        colAvg = Application.WorksheetFunction.Average(targetRange)

        

        If Err.Number = 0 Then

            ' 6. Paste at the bottom of THIS column

            lastRow = ActiveSheet.Cells(Rows.Count, col.Column).End(xlUp).Row

            With ActiveSheet.Cells(lastRow + 1, col.Column)

                .Value = colAvg

                .Font.Bold = True

                .Interior.Color = RGB(220, 230, 241) ' Light Blue highlight

            End With

        End If

        On Error GoTo 0

    Next col

End Sub