VBA Excel - find last row and sum more than one column

[agieseke74]

I am creating a new macro to enable a custom report from a download we use on a bi-weekly basis. Initially, I recorded the macro. This has given me everything I need, except for my last piece. I need to find the last row, go one row down, and sum columns J, K, L, and M. Then in column "I" the word "Total" should be on that same row. Through the record feature, it wants static rows as part of the formula. I need it to be dynamic.

To be clear, I want to sum column J from J2:Jxxx, where xxx is the last row. The same holds true for columns K through M. Again, the number of rows will vary with each report we download.

I need to know how to write this. I have searched several forums and excel sites to get this, but nothing has worked. Also, can this be done in such a way that one set of code will cover all the columns, or will it have to be repeated for each column?
Here is the code I have (keep in mind this is a hodge-podge from trying out various helps I found on-line throughout the day):
<code>
EndRowI = Range(I65536).End(xlUp).Row
.Sheets(x).Range("I" & EndRowI + 1).Formula = "=SUM(I2:I" & EndRowI & ")"
EndRowH = Range("H" & Rows.Count).End(xlUp).Row
Range("H" & LR + 1).FormulaR1C1 = "Total"</code>

For what it's worth, the name of my sheet is "combined" which is an earlier step in my macro.

Thank you!

 

[Macropod]

Try something based on:

Dim lRow As Long, lCol As Long
With ThisWorkbook.Worksheets("Sheet1")
  With .UsedRange.Cells.SpecialCells(xlCellTypeLastCell)
    lRow =.Row
    lCol = .Column 
  End With
  MsgBox "The used range spans " .Range("A1", .Cells(lRow, lCol)).Address
End With

 

[agieseke74]

I have continued to research this, and adjusted my macro. Below is the entire code for reference. The section in question is toward the bottom:

 Keyboard Shortcut: Ctrl+k
'
    Sheets("Original").Select
    Sheets("Original").Copy After:=Sheets(1)
    Sheets("Original (2)").Select
    Sheets("Original (2)").Name = "combined"
    Range("E2:E4").Select
    Selection.Copy
    Range("K1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Columns("O:O").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "Total"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
    Range("N2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FillDown
    Selection.End(xlUp).Select
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Range("$A$2:$O$2500").RemoveDuplicates Columns:=3, Header:=xlNo
    Columns("G:G").Select
    Selection.NumberFormat = "#,##0.00"
    Columns("J:M").Select
    Selection.NumberFormat = "#,##0.00"
    Columns("I:I").Select
    Selection.NumberFormat = "#,##0.00"
    Columns("H:H").Select
    Selection.NumberFormat = "0.0%"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS(Original!C[1],Original!C[-7],RC[-7],Original!C[-5],R1C10)"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS(Original!C[1],Original!C[-8],RC[-8],Original!C[-6],R1C11)"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS(Original!C,Original!C[-9],RC[-9],Original!C[-7],R1C12)"
    Range("J2:L2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FillDown
    Selection.End(xlDown).Select
    Range("I96:M96").Select
    EndRowI = Range(I1048576).End(xlUp).Row
    Range("I" & EndRowI + 1).Formula = "=SUM(I2:I" & EndRowI & ")"
    EndRowJ = Range(J1048576).End(xlUp).Row
    Range("J" & EndRowJ + 1).Formula = "=SUM(J2:J" & EndRowJ & ")"
    EndRowK = Range(K1048576).End(xlUp).Row
    Range("K" & EndRowK + 1).Formula = "=SUM(K2:K" & EndRowK & ")"
    EndRowL = Range(L1048576).End(xlUp).Row
    Range("L" & EndRowL + 1).Formula = "=SUM(L2:L" & EndRowL & ")"
    EndRowM = Range(M1048576).End(xlUp).Row
    Range("M" & EndRowM + 1).Formula = "=SUM(M2:M" & EndRowM & ")"
    EndRowH = Range("H" & Rows.Count).End(xlUp).Row
    Range("H" & LR + 1).FormulaR1C1 = "Total"
    Columns("I:M").Select
    Range("I65").Activate
    Columns("I:M").EntireColumn.AutoFit
    Range("A1").Select
    ActiveWorkbook.Save
End Sub

Currently, when I try to run this, I get a Run-time error '1004': Method 'Range" of object'_Global" failed. It then highlights the line beginning with "EndRowI = Range(I1048576)..." How do I correct this?