average function for macro

What I'm trying to do is just a simple average on a column of data. The data can have quite a variation in the number of cells that it fills. What I'm struggling with is getting the right code tolook to the first cell of data, then basically select all of the data in the column and give me the average. and display it in a different column
Like I said the amount of data will change from day to day, so it has to be a dynamic sort of cell range within the average function. Anybody have any ideas?

bascially need to convert this
= AVERAGE(F2:F23)
to one with not set rows as there might be more than 23 ot less than 23

need it to be done while thi function is running

Sub FUND_OWNERSHIP()Dim i As Long, LR As Long
Application.ScreenUpdating = False
On Error Resume Next


    
    
     Set ms = Workbooks("Book4.xlsx").Sheets("Allocation")
    With Worksheets("Allocation")


        LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
          For i = 1 To LR
             If IsDate(.Cells(i, 1)) Then
                 .Cells(i, 1).Copy
                MyDate = ms.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial(xlPasteValues)
                MyDate.NumberFormat = "dd/mm/yyyy"
            End If
                If UCase$(.Cells(i, 30).Value) = "FUND OWNERSHIP %" Then
                                              
                .Cells(i, "AD").Offset(1).Resize(4).Copy
                
                MyData = Format(ms.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial(Transpose:=True), Percent)
                    
                   


               
              End If
          Next i
          
          
    End With
       
Application.CutCopyMode = 0


Application.ScreenUpdating = True


End Sub

Just select whole range.

=AVERAGE(F:F) will give you average value of cells with numbers (ignoring text and blank cells).

Just use:

result = application.WorksheetFunction.Average(Range("F1:F65536"))

Try

Sheets("Sheet2").Range("C2") = application.WorksheetFunction.Average(Sheets("sheet1").Range("B1:B65536"))