PDA

View Full Version : VBA To Autofill a formula across multiple specific columns



simora
03-11-2020, 06:08 PM
I have some code which puts a formula after I have filterred some data.
I want to sum the visible cells in a set of columns, and put the totals 2 rows after the last rows in the columns.
This formula below works to sum the visible cells in column C, but when I Autofill it across columns, it copies the Total of Column C, but not the totals of the respective Columns.

How do I modify the formula to sum each Column in the range.

Here's the current code:



ActiveCell.Formula = WorksheetFunction.Sum(Range("C2:C" & Range("C65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible))

Set r1 = ActiveCell
Set r2 = r1.Offset(0, 11)

Selection.AutoFill Destination:=Range(r1, r2), Type:=xlFillDefault

simora
03-11-2020, 10:20 PM
Hi:

For anyone that might be interested, I re-structured the code :



Dim LCol As Integer
Dim LastRow As Long

LCol = 16
For col = 3 To LCol
LastRow = Cells(Rows.Count, col).End(xlUp).Row
colSum = WorksheetFunction.Subtotal(109, Range(Cells(2, col), Cells(LastRow, col)))
Cells(LastRow + 2, col) = colSum

Next col