Adding "TOTAL" and summing column

  • Thread starter Thread starter Fred Butterfield
  • Start date Start date
F

Fred Butterfield

I pull in an array of data and format it into a report.
Here's my problem:

In column H I need to insert the word "TOTAL" in the
second blank row below the last entry in the array.

In the same row, column J, I need to insert"=sum(j4:j
[whatever])" to sum the values in column J.

The number of rows can vary from 2 or 3 to several
thousand. Sometimes there are entries that take more than
one row for the data in the other columns, creatng empty
cells in H and J.

I've got everything else worked out except getting the
total in. Anyone have an idea or suggest a place to look?
 
The following code insert TOTAL and the SUM function:

'======================
Dim r As Long
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
r = ws.Cells(Rows.Count, 10).End(xlUp).Row + 2
ws.Cells(r, 8).Value = "TOTAL"
ws.Cells(r, 10).Formula = "=SUM(J4:J" & r - 2 & ")"
'=========================

Fred said:
I pull in an array of data and format it into a report.
Here's my problem:

In column H I need to insert the word "TOTAL" in the
second blank row below the last entry in the array.

In the same row, column J, I need to insert"=sum(j4:j
[whatever])" to sum the values in column J.

The number of rows can vary from 2 or 3 to several
thousand. Sometimes there are entries that take more than
one row for the data in the other columns, creatng empty
cells in H and J.

I've got everything else worked out except getting the
total in. Anyone have an idea or suggest a place to look?
Click to expand...
 
Fred

Try thnis

Code gets row number of last used cell in Column H


Dim LastRow As Long

LastRow = Cells(Rows.Count, "h") _
.End(xlUp).Row

Cells(LastRow + 2, "h").Value = "TOTAL"

Cells(LastRow + 2, "j").Value = _
"=sum(j4:j" & LastRow & ")
 
Back
Top