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

 

[gmorris]

Hello,

I'm new here but not new to Excel. Are you saying that you want to sum
a block range, and always put the total in the cell to the right of the
range but on the last row that contains data, and do it
programmatically?

It would depend on how many of these you had on one page. If there will
only be one block range per page like those, the process would be
easier.

In order to come up with a good solution, it would also be handy to
know how this data is getting there and when you want to sum it (or
check it if it is automated somehow). There are numerous ways to do
this, it's just finding the most efficient and easy one! You might
benefit from naming the ranges if that's possible for example. If you
could give a little more information I'd try to help more.

 

[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.