excel - VBA code to sum a column in a changing Table with the sum above the header

Question

I'm new to VBA. My macro recorder created this code when I added a sum for each month at the top of my table above the Header. When the table grew the sum did not include the new lines at the bottom so I put 800 in there to ensure I have the correct value (my table has 540 rows of data right now). This is a bandaid and I would like to fix the code so that it sums all values in the table for that column. Then I autofilled the formula across all 12 months.

Range("H2").Select
ActiveCell.FormulaR1C1 = "=SUM(R[2]C:R[800]C)"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:T2"), Type:=xlFillDefault
Range("H2:T2").Select

I found this:

Range("H4", Range("H1048576"),End(xlUp)).Select

which selects the right range for a table that is discontiguous and changes in size, but I don't know how to incorporate the Sum formula with this variable column range. Can someone help me with syntax? Thanks!

question from:https://stackoverflow.com/questions/65836057/vba-code-to-sum-a-column-in-a-changing-table-with-the-sum-above-the-header

Answer 1

Waitting for answers