Sum visible rows

p45cal · #1 06-19-2023, 02:39 PM

A selection of macros to put the formulae in place:

Code:

Sub blah()
Selection.Rows(1).Offset(-1).Formula = "=SUBTOTAL(109," & Selection.Columns(1).Address(0, 0) & ")"
End Sub

Code:

Sub blah2()
With Selection.Rows(1).Offset(-1)
  .Formula = "=SUBTOTAL(109," & Selection.Columns(1).Address(0, 0) & ")"
  .Value = .Value 'optional line to convert the formulae to plain values.
End With
End Sub

The first macro will put fomulae in the cells and their values will adjust immediately to what's hidden/not hidden.

In the second macro, if you remove the .Value = .Value line the same as above,
but if you leave that line in, they'll become plain values and will not adjust to hidden/not hidden rows, unless the macro is run again.

edit:
and another, more akin to what you originally asked:

Code:

Sub blah4()
Set visRng = Selection.Columns(1).SpecialCells(xlCellTypeVisible)
Selection.Rows(1).Offset(-1).Formula = "=sum(" & visRng.Address(0, 0) & ")"
End Sub

soroush.kalantari · #2 06-19-2023, 09:00 PM

Quote:

Originally Posted by p45cal

A selection of macros to put the formulae in place:

Code:

Sub blah()
Selection.Rows(1).Offset(-1).Formula = "=SUBTOTAL(109," & Selection.Columns(1).Address(0, 0) & ")"
End Sub

Code:

Sub blah2()
With Selection.Rows(1).Offset(-1)
  .Formula = "=SUBTOTAL(109," & Selection.Columns(1).Address(0, 0) & ")"
  .Value = .Value 'optional line to convert the formulae to plain values.
End With
End Sub

The first macro will put fomulae in the cells and their values will adjust immediately to what's hidden/not hidden.

In the second macro, if you remove the .Value = .Value line the same as above,
but if you leave that line in, they'll become plain values and will not adjust to hidden/not hidden rows, unless the macro is run again.

edit:
and another, more akin to what you originally asked:

Code:

Sub blah4()
Set visRng = Selection.Columns(1).SpecialCells(xlCellTypeVisible)
Selection.Rows(1).Offset(-1).Formula = "=sum(" & visRng.Address(0, 0) & ")"
End Sub

Thank for your reply, blah4 can do what I want.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Add Rows to table that will include content controls of previous rows	bobsagat	Word VBA	20	01-27-2020 08:00 AM
VBA color of visible gridlines of tables word (with some merged cells in first rows)	Alvaro.passi	Word VBA	0	07-18-2017 09:11 AM
How to find duplicate rows in a huge excel file containing 500000 rows	Stc25234	Excel Programming	2	06-23-2017 10:51 AM
Delete All empty Rows - Print - Undo all Rows deleted	Bathroth	Word VBA	1	10-01-2014 01:40 PM
Grouping table rows to prevent individual rows from breaking across pages	dennist77	Word	1	10-29-2013 11:39 PM