auto sum macro

gittymoe

Board Regular
Joined
Apr 23, 2005
Messages
79
I have been looking for a macro that will auto sum columns with variable lengths. Can someone point me in the right direction.

Thank You,
Don't sweat the small stuff just pet the sweaty stuff
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Why do you need VBA?

In a different column, try something like this:
=SUM(A:A)
 

gittymoe

Board Regular
Joined
Apr 23, 2005
Messages
79
I am using this in a macro that I have recored and although the layout of the spreadsheet is the same the length of the columns is variable....For example

Column A may be 25 rows one week and then the next week it may be 100 rows. I am looking for a VBA code that will sum at the end of 25 rows just the same as 100 rows.


Thank You,


Judd
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Judd,

seems like you are giving a bit more info, but you're not even replying to Hotpeppers suggestion
if you cannot use it, then explain why ?

I can guess from what you're writing ,that you want to put the SUM just beneath the data, but you were not clear about that

kind regards,
Erik
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
here is a macro that will put in the sum at the point of the cursor
Code:
Sub AutoSum_Formula()
    Dim x As String
    Dim y As String
    
        x = ActiveCell.Offset(-1, 0).End(xlUp).Address
        y = ActiveCell.Offset(-1, 0).Address
        
        ActiveCell = "=Sum(" & x & ":" & y & ")"
        
End Sub
 

gittymoe

Board Regular
Joined
Apr 23, 2005
Messages
79
Thanks Guys this works great.


Dim x As String
Dim y As String

x = ActiveCell.Offset(-1, 0).End(xlUp).Address
y = ActiveCell.Offset(-1, 0).Address

ActiveCell = "=Sum(" & x & ":" & y & ")"
 

Forum statistics

Threads
1,136,262
Messages
5,674,708
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top