Last Row Average VBA

[NocoffeeNoWorkee]

How can I put an average of the last row in the first empty column of the same row?

Here is what I tried:
[TABLE="class: outer_border, width: 800"]
<tbody>[TR]
[TD]Sub LastRowAve()
lastrow = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lColumn = ThisWorkbook.Sheets("sheet1").Cells(1, Columns.Count).End(xlToLeft).Column


ThisWorkbook.Sheets("sheet1").Range(lColumn + 1 & lastrow) = Application.WorksheetFunction.Average(ThisWorkbook.Sheets("sheet1").Range("A" & lastrow:lColumn&lastrow))


End Sub


[/TD]
[/TR]
</tbody>[/TABLE]

Theres a problem with the list seperator in Range.

 

[Tyler Hilton]

Not sure if this will work but maybe try this in the last line of code:

ThisWorkbook.Sheets("sheet1").Range(lColumn + 1 & lastrow) = Application.WorksheetFunction.Average(ThisWorkbook.Sheets("sheet1").Range("A" & lastrow & ":" & lColumn & lastrow))

Best,

Tyler Hilton

 

[NocoffeeNoWorkee]

Thanks for the reply.

I think that helped with the separator error, but there is still an 'application defined' error.

 

[igold]

Hi,

Does this do what you want...

Sub LastRowAve()
    Dim avg As Single
    Dim lastrow As Long, lColumn As Long


    lastrow = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    lColumn = ThisWorkbook.Sheets("sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
    
    avg = Application.WorksheetFunction.Average(Range(Cells(lastrow, 1), Cells(lastrow, lColumn)))
    ActiveSheet.Cells(lastrow, lColumn + 1).Value = avg


End Sub

HTH

igold

 

[NocoffeeNoWorkee]

That does do it. Thanks much.

 

[igold]

Glad to help!

 

[Rick Rothstein]

Hi,

Does this do what you want...

Sub LastRowAve()
    Dim avg As Single
    Dim lastrow As Long, lColumn As Long


    lastrow = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    lColumn = ThisWorkbook.Sheets("sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
    
    avg = Application.WorksheetFunction.Average([B][COLOR="#FF0000"]Range(Cells(lastrow, 1), Cells(lastrow, lColumn))[/COLOR][/B])
    ActiveSheet.Cells(lastrow, lColumn + 1).Value = avg


End Sub

Since this is VB code and not worksheet formulas, you don't have to worry about Circular Referencing, so you can replace the red highlighted part of your code with this... Rows(lastrow)

 

[igold]

@Rick,

Thanks for the tip. That makes perfect sense...

igold