Determine last row then sum various columns by row

Hello,

I am creating a spreadsheet that takes a data input of a set # of columns but a dynamic # of rows 1-65536 into worksheet1, and performs functions on this data in worksheet2

My problem is i dont know VBScript.

I have read on this forum where [ lastrow = Range("A65536").End(xlUp).Row ] will tell me the last row # but thats not helpful becasue i do not know how to use the vbscript interface to accomplish the next task: summing various columns from row 4 to the last row.

I need to be able to access this "lastrow" variable from a formula in a cell.

Once i know how many rows there are, i need for the cell below last row with data in column 'U' to =Sum(U4,lastrow) for that column

Can anyone help me, time is of the essence.

bump

No one knows anything about this?

Ok i give up.... i cant figure this out without using VBscript...


No one has any ideas or suggestions

You can use the below userdefined function which will return last row.

you can use in any cell formulae

like in cell c2 =(c1*last_row())

you have copy the below in vba module.

alt+f11, create a module and paste the below code.

you call the function from any cell by using =last_row()

Public Function last_row()
Dim r As Range
Set r = ActiveSheet.UsedRange
t = r.Address
t1 = Split(t, ":")
If UBound(t1) > 0 Then
last_row = Range(t1(1)).Row
Else
last_row = 1
End If
End Function