Automatic Block

[sylbalm]

I'm fairly new to Excel and am working on a cabinetry estimating spreadsheet. I want to automatically create a block of cells with the totals of everything two or three rows below everything I've entered. Sometimes the spreadsheet will be 20 rows, sometimes 50. I've made a group of cells that add everything up, add tax, subtract discounts, etc. and I want it to always be a couple of rows below the last thing I've entered without constantly having to cut and paste or add rows. Is this possible?

Thanks

 

[ravishankar]

Hi
Paste the following codes in the macro window ( Alt F11)
sub total()

x= cells(rows.count,1).end(xlUp).row
y = cells(1,columns.count).end(xltoleft).column
for a = 1 to y
b = chr(a+64)
c = b & "1:" & b & x
cells(x+3,a) = "=sum(" & c & ")
next a
end sub

run the macro. It will total each column 3 rows below the last row.
Ravi

 

[Scott Huish]

ravishankar:

That will fail at Column AA, but there is no need for a loop:

sylbalm:

Perhaps:

Sub test2()
Dim x As Long, y As Long
x = Cells(Rows.Count, 1).End(xlUp).Row + 3
y = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(x, 1), Cells(x, y)).Formula = "=SUM(A1:A" & x - 3 & ")"
End Sub

 

[ravishankar]

Hi
Thanks hotpepper.
I am aware that it works from a to z only. I have written codes to cope up with columns upto IV . I thought unless the board member specifies the no. of columns, i could use a single alphabet. also I have noted the improvement you have done in avoiding the loop to copy the data.
Ravi