Getting the Sum Total of a variable Range

[wutzke]

If I have a a series of values in worksheet across several rows and
columns, is there a way programically find the total if the exact
number of columns and rows to be summed up is not known?

Say that on the 1st sheet b2:g4 have values in them. Row 5 & Column H
are blank, but h4 would be the place to put the sum total - being it
is the end of the range. In the 2nd sheet the values are b2:L7, with
Row 8 & Column M blank. The total would be placed in M7Values are
either Numeric or True/False.

A user function would be written named "SumScale"

example spreadsheet is at
http://spreadsheets.google.com/ccc?key=p5XvDvb4SUVjosHuUTsXuJw

 

[Jacob Skaria]

Try and feedback

Sub SumScale()
lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
lngLastCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
ActiveSheet.Cells(lngLastRow, lngLastCol + 1).Formula = _
WorksheetFunction.Sum(Range(Cells(2, 2), Cells(lngLastRow, lngLastCol)))
End Sub

If this post helps click Yes

 

[Rick Rothstein]

Try this UDF (just put it in the cell next to the last cell in your rangeyou
want the sum to be in; for example, put it in H4 if your range is B2:G4)...

Function SumScale() As Double
Dim FirstRow As Long
Dim FirstCol As Long
With ActiveCell
FirstRow = .Offset(, -1).End(xlUp).Row
FirstCol = .Offset(, -1).End(xlToLeft).Column
SumScale = WorksheetFunction.Sum(Range(Cells( _
FirstRow, FirstCol), .Offset(, -1)))
End With
End Function

 

[Rick Rothstein]

There is a problem with the UDF that I just posted... if won't update if you
change values inside the range it applies to; and, if I modify it to try and
do that, then it won't work correctly. Try this UDF instead (but make sure
to read the note at the end of this message)...

Function SumScale(Cell As Range) As Double
Dim C As Range
Dim FirstRow As Long
Dim FirstCol As Long
Application.Volatile
Set C = Cell.Offset(, -1)
FirstRow = C.End(xlUp).Row
FirstCol = C.End(xlToLeft).Column
SumScale = WorksheetFunction.Sum(Range(Cells(FirstRow, FirstCol), C))
End Function

Note: This UDF takes an argument and that argument is the cell address that
you place it in. So, for your first example, assuming you have values in
B2:G4, you would put this formula in H4

=SumScale(H4)

 

[wutzke]

can I create a variable in the UDF that holds the sum?

 

[wutzke]

I might have a worksheet with several sheets with differing blocks of
values. Some may be as small as 2 rows by 5 columns, others may by as
large as 6 rows by 14 columns. I can control the starting cell, B2 in
these examples.

So naming each range doesn't seem a solution, as I don't know with
each set what the cells will be.

I would like to capture said range within the macro, for each sheet
and reference it again.

 

[Rick Rothstein]

I'm not sure what you mean... a UDF is a function that returns a value and
then closes down... can you explain in more detail what you are trying to do
and why you think you need to "hold the sum" in a variable? I would also ask
you... are you definitely looking for a UDF (as you asked for in your
initial post) or are you looking for a macro (you mentioned "macro" in your
response to gmorris)? Perhaps if you tell us exactly what you are trying to
do, what you need will become clearer.

 

[littleredhairedgirl]

Good discussion. To close to flaming.
Seems pretty clear. Jacob's solution works. Rick's not so much. If you
didn't know where the last cell was, how could you put a formula in
it.

 

[Rick Rothstein]

"Close to flaming"? Are you kidding? First off, the ORIGINAL question asked
for a User Defined Function (UDF)... note the word "function"... Jacob
posted a macro, which is not a function, it is a subroutine... UDF's work
automatically, macros need to be called individually. Second, the sample
workbook the OP posted showed a worksheet that had three sets of ranges that
needed to be summed up... Jacob's macro, as written, will not work on that
worksheet. Third, Jacob's macro does not "hold the sum" in a variable as the
OP later asked me; so, for the last two reasons, I'm not completely sure
Jacob's solution "works" in the way the OP wanted. Fourth, as for knowing
where to place the formula... I figured since the OP asked for a UDF, the
formula was to be placed "by eye" and, as data was added (assuming data
would be added), the formula could be moved easily enough (click/drag the
cell's border) to the new location. Truthfully, in total, I was confused as
to what the OP actually wants (maybe needs is a better word), so I asked.
Why do you consider that "close to flaming"?

--
Rick (MVP - Excel)


Good discussion. To close to flaming.
Seems pretty clear. Jacob's solution works. Rick's not so much. If you
didn't know where the last cell was, how could you put a formula in
it.