change the range of function automatically when rows are added

user_18359433 0
Viewed 98

[pic1: the original one1 pic2: when I deleted the rows pic3: when I added rows again and inserted numbers

I wrote =sum(A2:A11) in cell A1, and I wrote random numbers in A2:A11. Then I deleted some rows and then the A1 cell's range changed automatically. But I don't understand why the range does not change automatically when I add new rows and intert new values. How can I make it change automatically? Do I have to use vba to do this?

excel-formula vba excel
2 Answers

A Worksheet Change Event: Monitor Change in Column's Data

  • I personally would go with JvdV's suggestion in the comments.
  • On each manual change of a cell, e.g. in column A, it will check the formula =SUM(A2:ALastRow) in cell A1 and if it is not correct it will overwrite it with the correct one.
  • You can use this for multiple non-adjacent columns e.g. "A,C:D,E".
  • Nothing needs to be run. Just copy the code into the appropriate sheet module e.g. Sheet1 and exit the Visual Basic Editor.

Sheet Module e.g. Sheet1 (not Standard Module e.g. Module1)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    UpdateFirstRowFormula Target, "A"
End Sub

Private Sub UpdateFirstRowFormula( _
        ByVal Target As Range, _
        ByVal ColumnList As String)
    
    On Error GoTo ClearError
    
    Dim ws As Worksheet: Set ws = Target.Worksheet
    Dim Cols() As String: Cols = Split(ColumnList, ",")
    
    Application.EnableEvents = False
    
    Dim irg As Range, arg As Range, crg As Range, lCell As Range
    Dim n As Long
    Dim Formula As String
    
    For n = 0 To UBound(Cols)
        With ws.Columns(Cols(n))
            With .Resize(.Rows.Count - 1).Offset(1)
                Set irg = Intersect(.Cells, Target.EntireColumn)
            End With
        End With
        If Not irg Is Nothing Then
            For Each arg In irg.Areas
                For Each crg In arg.Columns
                    Set lCell = crg.Find("*", , xlFormulas, , , xlPrevious)
                    If Not lCell Is Nothing Then
                        Formula = "=SUM(" & crg.Cells(1).Address(0, 0) & ":" _
                            & lCell.Address(0, 0) & ")"
                        With crg.Cells(1).Offset(-1)
                            If .Formula <> Formula Then .Formula = Formula
                        End With
                    End If
                Next crg
            Next arg
            Set irg = Nothing
        End If
    Next n

SafeExit:
    If Not Application.EnableEvents Then Application.EnableEvents = True
    Exit Sub
ClearError:
    Debug.Print "Run-time error '" & Err.Number & "': " & Err.Description
    Resume SafeExit
End Sub
user_98140690

Use a nested function as below: =SUM(OFFSET(A2,,,COUNTA(A2:A26)))

user_151552780
Related