Sum column with variable rows

**samz93** · 03-05-2013, 08:05 PM

hi
im new to VBA and am having difficulty trying to sum values in a column however the number of rows that contain a value in that column is a variable. i would like to sum E19 onwards and return the value in J8.

would greatly appreciate any help.
thanks in advance!

**Richard Buttrey** · 03-05-2013, 08:27 PM

Hi,

Have you considered just specifying the maximum number of rows you're ever likely to need? e.g. SUM(E19:E1000)

Are you saying you already have a VBA variable and it contains the number of rows that you want to use, or that you need to create the variable.

If the former then

Sheet1.Range("J8") = "=Sum(E19:E" & myvariable + 19 & ")"

If the latter and you want the last used row in a column as the variable then

Dim myVariable as Long
myVariable = Range("E" & Rows.Count).End(xlup).Row
Sheet1.Range("J8") = "=Sum(E19:E" & myvariable  & ")"

Adjust the sheet1 ref as necessary.

**samz93** · 03-06-2013, 02:16 PM

this is extremely helpful. sorry to be a pain but how would i go about applying this so it works for the whole workbook

**Richard Buttrey** · 03-06-2013, 06:09 PM

Hi,

Can you clarify what you mean please.

When you say how do you apply it to the whole workbook are you wanting to know how you can copy one of the formulae I gave you to each sheet in the workbook?

If so are you saying that the number of rows which need summing on each worksheet are all different - or may be different?

As I said if you use the first formula you don't need to worry about whether the number of rows varies - just sum the whole column. Otherwise you'll need to change the row range in the formula with a macro. And in which case what should be the trigger for that macro? A button push, a change in a cell (which cell?).

As I say if the first formula is acceptable then it just needs copying to each sheet. In which case the simplest way, which avoids writing a macro is to Group all relevant sheets by holding down the Ctrl key and clicking each sheet tab name with a mouse click, then type the formula into J8 on one sheet (any will do) and it will be entered into all the other Grouped sheets.