Calculating Average using a Variable Range

[VistaOnMyMac :)]

I need to use the average function in the row after the last row of data in
column B that calculates the average of range (B10:last cell of data in that
column). Then I need to fill that row to the right with that same average
function, stopping the fill at the last column of data.

This spreadsheet for my teachers will vary in size and my novice ability
with VB has me stuck. I would really appreciate any help with this.

 

[Bob Phillips]

Public Sub ProcessData()
Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
LastCol = .Cells(10, .Columns.Count).End(xlToLeft).Column
.Cells(LastRow + 1, "B").Resize(, LastCol - 1).Formula =
"=AVERAGE(B10:B" & LastRow & ")"
End With

End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[VistaOnMyMac :)]

Bob,

Thank you so much. It worked great! How would I add formatting tasks to
the same cells. For example:
..NumberFormat = "0.0"

 

[Bob Phillips]

Public Sub ProcessData()
Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
LastCol = .Cells(10, .Columns.Count).End(xlToLeft).Column
With .Cells(LastRow + 1, "B").Resize(, LastCol - 1)

.Formula = "=AVERAGE(B10:B" & LastRow & ")"
.NumberFormat = "0.0"
End With
End With

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)