Summing Columns of Varying Length from a Constant cell to the bottom of the column - 5 rows

[amirbp2002]

Hi everyone,

I have been trying to figure this out, I need a macro to start from a constant cell in a column (Ex: A12), then sum the column down to the bottom (varying length) as in the command+shift+down keystroke, but shorten the sum selection by 4 cells from the bottom.

So, for instance if the column is filled with numbers down to A1000, the it sums from A12 to A996.

Thank you very much!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub SumCol()
   Dim rng As Range
   Set rng = Range("A12", Range("A12").End(xlDown).Offset(-4))
   MsgBox Application.SUM(rng)
End Sub

 

[amirbp2002]

Thank you! I wanted it to print the sum at the bottom of the column (lets say 1001).

Thanks

 

[amirbp2002]

Or actually, it is better the sum value to be printed in another sheet in the same excel (Sheet1), in the first cell (A1), I think that would be easier.

Thanks

 

[Fluff]

How about

Sub SumCol()
   Dim rng As Range
   With Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")
      Set rng = .Range("A12", .Range("A12").End(xlDown).Offset(-4))
   End With
   Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("A1").Value = Application.SUM(rng)
End Sub

Change sheet names in red to suit

 

[amirbp2002]

Works great! Thank you very much. Just wondering for the future, is there anyway I can record keystrokes in macro excel recorder? I would perform this in the recorder, but it unfortunately only picked up the cell number rather than the key strokes, so I couldn't apply it to a column of varying length.

 

[amirbp2002]

Also, Can I loop this through lets say 9 columns x spaced apart so it picks up the sum for A column, then do the same for I column, and so on to then print it in A2, A3, and so on in Sheet1?

Thanks

 

[Fluff]

You would not be able to record a sum on a dynamic range, as the recorder is very literal.
But you can record a dynamic range by selecting a start cell & then using Ctrl Shift down arrow