Help: Dynamic Total Updates?

[Matt]

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

 

[Trevor Shuttleworth]

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]

Thanks, this solves part of the situation nicely

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

 

[Trevor Shuttleworth]

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

 

[Matt]

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]

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]

thanks for the UDF, this is exactly what i was looking for!

 

[Trevor Shuttleworth]

You're welcome. Thanks for the feedback.

thanks for the UDF, this is exactly what i was looking for!

 

[Trevor Shuttleworth]

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