• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Sum to Last Row and Last Column with Heading

Hi I am using the Excel 2013

I need the VBA for sum with heading

The Data may be increase or decrease in row or column (unknown range), but I need the sum formula in the last row and column with the heading. the heading is "Grand Total"

Kindly provide the VBA Code
 

Attachments

Last edited:

shrivallabha

Excel Ninja
Hi I am using the Excel 2013

I need the VBA for sum with heading

The Data may be increase or decrease in row or column (unknown range), but I need the sum formula in the last row and column with the heading. the heading is "Grand Total"

Kindly provide the VBA Code
Here's a code to start with comments.

Code:
Public Sub CreateSumFormulas()
Dim lngLastCol As Long, lngLastRow As Long
'\\ Find out last row and column
lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
lngLastCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
'\\ Insert fixed titles
Cells(lngLastRow, 1).Value = "Grand Total"
Cells(1, lngLastCol).Value = "Grand Total"
'\\ Insert formula
Range(Cells(2, lngLastCol), Cells(lngLastRow - 1, lngLastCol)).Formula = "=SUM(B2:" & Cells(2, lngLastCol - 1).Address(0, 0) & ")"
Range(Cells(lngLastRow, 2), Cells(lngLastRow, lngLastCol)).Formula = "=SUM(B2:" & Cells(lngLastRow - 1, 2).Address(0, 0) & ")"
End Sub
 
Top