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.

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.

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.

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.

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.

.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

(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

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.

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.

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.

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.

' 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.