Calculating Average using a Variable Range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

VistaOnMyMac :) - 27 Mar 2008 15:09 GMT

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.

Reply to this Message

Bob Phillips - 27 Mar 2008 15:28 GMT

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

Signature

---
HTH

Bob

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

>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
[quoted text clipped - 4 lines]
> 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.

Reply to this Message

VistaOnMyMac :) - 28 Mar 2008 15:09 GMT

Bob,

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

Reply to this Message

Bob Phillips - 29 Mar 2008 01:05 GMT

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

Signature

---
HTH

Bob

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

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

Reply to this Message