using first and last row in Sum formula

[saschmeling]

Have several regions on a sheet- trying to sum up cells in each region.

Can determine the row numbers for the first and last rows of each region but am looking for a way to use that in a sum formula.

Here is where I'm at:

Range("A8").Select
myRow = ActiveCell.CurrentRegion.End(xlDown).Row
firstRow = ActiveCell.CurrentRegion.Rows(1).Row
Cells(myRow + 1, 4).Select

in the selected cell, I would like to put a formula that will add all the cells from myRow:firstRow in the active column.

have tried this:

ActiveCell.FormulaR1C1 = "=SUM([firstRow]c[myRow]c)"

No Joy, Any suggestions?

Thanks,

 

[SydneyGeek]

You can do it quite simply using a range name, assuming that you want to add everything from the top of a section to the row above your formula.
Select (for example) A6 and then create a named range called RowAbove, whose range is =A5 (note, this is a relative reference, not absolute).

Then the formula becomes =Sum([FirstRow]:RowAbove)

Denis

 

[saschmeling]

Hi Denis,

Not sure how to do this when the range is dynamic. Each section has a different sized range and the range will change every month. Also, thought I did something like this with the code:

myRow = ActiveCell.CurrentRegion.End(xlDown).Row
firstRow = ActiveCell.CurrentRegion.Rows(1).Row

Thanks,

 

[SydneyGeek]

One option is to create a custom function. Paste this into a new module in your workbook.

Function SumIntersect(Rng1 As Range, Rng2 As Range) As Double
    Dim rng As Range
    
    Set rng = Intersect(Rng1, Rng2)
    SumIntersect = WorksheetFunction.Sum(rng)
    Set rng = Nothing
End Function

To get the sum of cells in Range1 and column B, use the formula = SumIntersect(Range1,B:B)

NOTE: There is no error handling. If you try to sum 2 ranges that don't intersect you will get a #VALUE! error.

Denis