Just discovered that SUBTOTAL(109,range) works like SUBTOTAL(9,range) but
excludes hidden rows, not *just* rows hidden by AUTOFILTER.

I would suggest this is probably more effective than the UDF I provided,
good though that was ;-)

Regards

Trevor


"Matt" wrote in message
ups.com...
thanks for the UDF, this is exactly what i was looking for!

Trevor Shuttleworth wrote:
Sorry, assumed you were filtering. You could try this home-grown UDF if
it
helps.

Function SumVisible(rRange As Range) As Long
Dim Cell As Range
Application.Volatile
SumVisible = 0
For Each Cell In rRange
If Cell.EntireRow.Hidden = False Then
If IsNumeric(Cell.Value) Then
SumVisible = SumVisible + Cell.Value
End If
End If
Next 'Cell
End Function

There's not much in the way of error handling but it might give you a
start.

Regards

Trevor


"Matt" wrote in message
ps.com...
subtotal doesn't work, i've tried it, its because i'm not using filters
(i've tested it as i thought it would work myself)

if necessary i have a hidden sheet that can store temp values, hoping i
don't have to use this sheet though because i'm trying to remove it
completely

the hidden rows are never going to be the same unfortunately, as i know
how i can handle that

Trevor Shuttleworth wrote:
Use SUBTOTAL instead of SUM, You'll need to check the help for the
variant
that you need


"Matt" wrote in message
oups.com...
Thanks, this solves part of the situation nicely

any ideas how to implement a function that only totals the visible
rows?

Trevor Shuttleworth wrote:
Try

Dim LastRow As Long
With Worksheets("Data")
LastRow = Range("H65536").End(xlUp).Row
.Cells(LastRow, 8).Formula = "=SUM(H2:H" & _
LastRow - 1 & ")"

.Cells(LastRow, 9).Formula = "=SUM(I2:I" & _
LastRow - 1 & ")"

.Cells(LastRow, 10).Formula = "=SUM(J2:J" & _
LastRow - 1 & ") + " & _
.Cells(LastRow, 6).Value
End With

Note that you would need a formula or something to exist in the
cell
in
column H where you want to put the updated formula ... otherwise
this
will
overwrite the bottom row of data. And the last row in column F
needs
to
have a number in it.

Regards

Trevor


"Matt" wrote in message
oups.com...
Sorry for posting this again, previous posting of it never got a
response from anybody and I need to get it solved.

I have a macro that adds new rows and copies formulas to these
new
rows, I need to make my totals update to include all the new
rows,
but
the number of rows added is not static

This is what i have for trying to update the formulas when new
rows
are
added:
With Worksheets("Data")
.Cells(.UsedRange.Rows.Count, 8).FormulaR1C1 = "=SUM(H2:" & _
.Cells((.UsedRange.Rows.Count - 1), 8) & ")"

.Cells(.UsedRange.Rows.Count, 9).FormulaR1C1 = "=SUM(I2:" & _
.Cells((.UsedRange.Rows.Count - 1), 9) & ")"

.Cells(.UsedRange.Rows.Count, 10).FormulaR1C1 = "=SUM(J2:" & _
.Cells((.UsedRange.Rows.Count - 1), 10) & ") +" _
& .Cells(.UsedRange.Rows.Count, 6).Value

End With

Obviously, it doesn't work as submitted. I'm pretty sure the
problem
is
in the formula, specifically, when I try to reference the new
range
i
want to work with. I have an idea of what the problem is, I just
can't
for the life of me figure out how to change it to make it work
properly

I would also like to make these formulas instead only display the
sum
of the visible rows (on the last formula, i would also like it to
only
add the cell in column 6 if all the rows are visible, note there
is
a
column hidden that will always remain hidden)

Subtotal will not work because i'm not using filters when the
rows
are
hidden

thanks for the help everybody, you've all been great with every
question i've asked in the past