vba dynamic sum based on dynamic range values

Question

I would like to thank everyone for their feedback so far it has helped a great deal. one question that I am grappling with is how to make my column values even so I can do a dynamic subtototal.

    Column k       Column R  Column Y    Column AF     Column AM      Column AT

       1                2         4           2             3              5
       3                9         7           8             2              4
       2                3         6           3             5              8
       3                          3                         2
                                  5
 TOT   9                14       25          13             12             17

Column k       Column R  Column Y    Column AF     Column AM      Column AT

       1                2         4           2             3              5
       3                9         7           8             2              4
       2                3         6           3             5              8
       3                          3                         2
                                  5
 TOT   9                14       25          13             12             17

on a monthly basis the column values can fluctuate, the question is how do I use VBA to create a dynamic sum based on the column with the most values.

Dim Rin As Range
Dim Rout As Range
Dim lRa As Long
lRa = Range("i" & Rows.count).End(xlUp).Row
Set Rin = ws.Range("i" & lRa)
Set Rout = ws.Range("I" & lRa)

aCell.Range("I11:P12", "R12:AY12").Copy

Rout.Offset(2, 0).Resize(1, 28).Formula = "=SUBTOTAL(9," & 
Rin.Address(0, 0) & ")"
lR = ws.Range("I" & Rows.count).End(xlUp).Row - 1 ' Finds the last blank   
row
ws.Range("I" & lR).PasteSpecial xlPasteFormats

Answer 1

If you know where your data starts you can use a method such as that given by Shai Rado.

You can't have any entirely empty rows or columns in the range.

You can then feed this lastRow variable into the range address for adding your subtotal formula.

Example: If your data is a continuous set of populated columns starting at Cell D3 the following will get the last used row number in the range of columns:

Option Explicit

Public Sub AddSubTotal()

    Dim lastRow As Long
    Dim lastCol As Long
    Dim firstRow As Long
    Dim rowHeight As Long
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet2")             'change as appropriate

    With ws.Range("D3").CurrentRegion 'change as appropriate
        firstRow = .Rows(1).Row
        lastRow = .Rows(.Rows.Count).Row
        lastCol = .Columns(.Columns.Count).Column
        rowHeight = lastRow - firstRow + 1     
    End With

    With ws
        .Range(.Cells(lastRow + 1, "D"), .Cells(lastRow + 1, lastCol)).FormulaR1C1 = "=SUBTOTAL(9,R[-" & rowHeight & "]C:R[-1]C)"
    End With

End Sub

If you need a different method to find the last used row and last used column there a lots of available resources online including my favourite by Ron De Bruin Find last row, column or last cell. The appropriateness of each method is determined by the shape and properties of your range e.g. no blank columns or rows in range. So choose a method that is right for your data or that can test for different likely cases and apply different methodologies as appropriate.

It is quite difficult to give a definitive answer (and i don't want to code lots of different possible scenarios) to this question without knowing more about the nature and variability of the data. If you familiarise yourself with the methods of finding last row and column you will be able to implement those that suit your needs.