#8
|
||||
|
||||
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 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 |
Tags |
formula, visible |
|
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 |