Find last cell in column then SUM in cell directly below
 

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.

Find last cell in column then SUM in cell directly below
 

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.

"Giggly4g" wrote in message
...
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.

Find last cell in column then SUM in cell directly below
 

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
"Giggly4g" wrote:

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.

Find last cell in column then SUM in cell directly below
 

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.

--
Biff
Microsoft Excel MVP


"Giggly4g" wrote in message
...
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.

Find last cell in column then SUM in cell directly below
 

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?

"Mike" wrote:


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
"Giggly4g" wrote:

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.

Find last cell in column then SUM in cell directly below
 

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


"Giggly4g" wrote:

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.

Find last cell in column then SUM in cell directly below
 

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)

"Giggly4g" wrote in message
...
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?

"Mike" wrote:


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
"Giggly4g" wrote:

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.

Find last cell in column then SUM in cell directly below
 

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 & ")"

"Mike" wrote:

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


"Giggly4g" wrote:

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.

Find last cell in column then SUM in cell directly below
 

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

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

Find last cell in column then SUM in cell directly below
 

I'm not sure I understand, but try:

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

"Giggly4g" wrote in message
...
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 & ")"

"Mike" wrote:

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


"Giggly4g" wrote:

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.

Find last cell in column then SUM in cell directly below
 

(angelic chorus singing in the backgroun)

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

"Rick Rothstein (MVP - VB)" wrote:

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

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

Find last cell in column then SUM in cell directly below
 

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

"Giggly4g" wrote:

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 & ")"

"Mike" wrote:

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


"Giggly4g" wrote:

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.