Find last cell in column then SUM in cell directly below

[Guest]

Hi! I need to locate the last cell with data in a column then go to the cell
directly beneath it and add in the sum function. The GoTo blank function
doesn't work because some of the cells within rows are blank. The number of
rows varies from day-to-day. I need to do this for several columns of the
worksheet. Your assistance is greatly appreciated.

 

[Dave Thomas]

Suppose the column is A. One quick way is to go to (F5) the last row in the
column, A65536 for Excel 2003, A1048576 for Excel 2007 and the press CTRL+UP
ARROW. This will take you to the last used row in column A.

 

[Guest]

Sub sumColumA()
With Worksheets("Sheet1")
If IsEmpty(.Cells(.Rows.Count, 1)) Then
With .Cells(.Rows.Count, 1).End(xlUp)
.Offset(2, 0).Formula = "=Sum($A$1:" & _
.Address & ")"
End With
End If
End With
End Sub

 

[T. Valko]

Put your sum formula at the top of the column then you don't have to worry
about where the end of the data is.

 

[Guest]

Looks like I'm almost there. Let's say one of the columns I want to sum is G.
So I modified the code to read like so (VBA didn't like the underscore that
you included so I deleted it)...

..Offset(2, 0).Formula = "=Sum($G$1:" &
.Address & ")"

What I end up with is "Sum($G$1$A$86)" appearing two cells down from the end
of column A. Two questions...1) how can I get the sum to show at the end of
column G and 2) what is wrong with the formula that it shows up this way?

 

[Guest]

Try This
If IsEmpty(.Cells(.Rows.Count, 7)) Then
With .Cells(.Rows.Count, 7).End(xlUp)

 

[Dave Thomas]

In the code:

If IsEmpty(.Cells(.Rows.Count, 1)) Then

The Cells property has the format Cells(RowIndex, ColumnIndex) so the code
above is
referring to column 1 - ie. column A. That is why you're getting $A$86 in
your formula.
To refer to column G you have to change the 1 to a 7 as in: If
IsEmpty(Cells(Rows.Count, 7)

 

[Guest]

That gets the entry into the correct area. Thank you!

Now I just need to figure out why the resulting cell is showing the
following...

"Sum($G$1$G86)" this is not a formula by the way; it is coming in as a
text entry.

I took a look at what the code looks like when you use the SUM function...

"ActiveCell.FormulaR1C1 = "=SUM(R[-87]C:R[-2]C)"

I tried adding in the additional = but received an error message when I ran
the macro. So, what is wrong with this line?

..Offset(2, 0).Formula = "Sum($G$1 & .Address & ")"

 

[Rick Rothstein \(MVP - VB\)]

.Offset(2, 0).Formula = "=Sum($A$1:" & _

Looks like I'm almost there. Let's say one of the columns I want to sum is
G.
So I modified the code to read like so (VBA didn't like the underscore
that
you included so I deleted it)...

The underscore is VB's line continuation character (provided it is at the
end of the line and preceded by a space). It means the line it's on and the
next line form a single line. So, this...

..Offset(2, 0).Formula = "=Sum($A$1:" & _
.Address & ")"

is the same as this...

..Offset(2, 0).Formula = "=Sum($A$1:" & .Address & ")"

Rick

 

[Vasant Nanavati]

I'm not sure I understand, but try:

..Offset(2, 0).Formula = "=Sum($G$1" & ":" & .Address & ")"
_________________________________________________________________________

That gets the entry into the correct area. Thank you!

Now I just need to figure out why the resulting cell is showing the
following...

"Sum($G$1$G86)" this is not a formula by the way; it is coming in as a
text entry.

I took a look at what the code looks like when you use the SUM function...

"ActiveCell.FormulaR1C1 = "=SUM(R[-87]C:R[-2]C)"

I tried adding in the additional = but received an error message when I
ran
the macro. So, what is wrong with this line?

.Offset(2, 0).Formula = "Sum($G$1 & .Address & ")"

Try This
If IsEmpty(.Cells(.Rows.Count, 7)) Then
With .Cells(.Rows.Count, 7).End(xlUp)

 

[Guest]

(angelic chorus singing in the backgroun)

Thank you all so very much! I truly appreciate your help and guidance!!!!

 

[Guest]

' Make sure you dont have a ' after the " and before the =
' like below
' "'=Sum($A$1:" & .Address & ")"

That gets the entry into the correct area. Thank you!

Now I just need to figure out why the resulting cell is showing the
following...

"Sum($G$1$G86)" this is not a formula by the way; it is coming in as a
text entry.

I took a look at what the code looks like when you use the SUM function...

"ActiveCell.FormulaR1C1 = "=SUM(R[-87]C:R[-2]C)"

I tried adding in the additional = but received an error message when I ran
the macro. So, what is wrong with this line?

.Offset(2, 0).Formula = "Sum($G$1 & .Address & ")"

Try This
If IsEmpty(.Cells(.Rows.Count, 7)) Then
With .Cells(.Rows.Count, 7).End(xlUp)