CODEWITHSUNDEEP
vbaexcel
Audrey Gieseke
Audrey Gieseke

Reputation: 11

Find last row and sum more than one column

I am creating a new macro to enable a custom report from a download we use bi-weekly.

I recorded the macro using one of the downloads, 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.

To be clear, I want to sum column J from J2:Jxxx, where xxx is the last row.

Each time we download this report, the number of lines will vary, so I cannot use static row numbers as part of the formula.

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 now a hodge-podge from trying out various helps I found on-line throughout the day):

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"

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

Thank you!

Upvotes: 1

Views: 1130

Answers (1)

Mertinc
Mertinc

Reputation: 781

*Please correct your worksheet name as my example refers to Sheet1 in ThisWorkbook

This code finds the biggest row number of J:M columns range and then sums each column and shows them at that biggest row number with their TOTAL title in H column. (As your question was not so clear I tried to figure out this based on your comments.)

Option Explicit

Sub SubUntilLastRow()
Dim CurCal As XlCalculation
Dim wb As Workbook, ws As Worksheet, colsLastRow As Long
Dim cols As Variant, SumCols As Long, colsArray As Variant
Dim biggestRow As Long

Application.ScreenUpdating = False
CurCal = Application.Calculation
Application.Calculation = xlCalculationManual

biggestRow = 1

Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")

colsArray = Array("J", "K", "L", "M")

For Each cols In colsArray
colsLastRow = ws.Cells(Rows.Count, cols).End(xlUp).Row
If colsLastRow > biggestRow Then
biggestRow = colsLastRow + 1
End If
Next cols

For Each cols In colsArray
colsLastRow = ws.Cells(Rows.Count, cols).End(xlUp).Row
ws.Cells(biggestRow, cols).Formula = "=SUM(" & cols & "2:" & cols & colsLastRow & ")"
Next cols

ws.Range("H" & biggestRow).Value = "TOTAL"

Application.ScreenUpdating = True
Application.Calculation = CurCal


End Sub

Upvotes: 1

Related Questions