Microsoft Office Forums - View Single Post

soroush.kalantari · 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.