Add sum to dynamic range in Macro
 

I download a report into Excel and I have created a macro to reformat it so
it is easy to read.

I want to add totals at the bottom of each section break. The problem I have
is that I don't know how big each section is as it changes from month to
month. when I add the section breaks I use;

ActiveCell.Offset(2, 0).End(xlDown).Resize(10, 14).Select
Selection.ClearContents
ActiveCell.Resize(7, 14).Select
Selection.Delete Shift:=xlUp

Which takes the cells I don't want and deletes and clears them out leaving 3
blank lines.

There are several of these sections of various lengths and I want to add
total to the bottom of each of them, there are 6 columns in each section.

How do I get the formula to add all cells above up to the first blank cell?

Thanks in advance
Rick

Add sum to dynamic range in Macro
 

It is always better to use intermediate variables that are descriptive so you
can document the code easily.

Set HeaderCell = ActiveCell
Set FirstdataCell = HeaderCell.Offset(2, 0)
Set LastDataCell = FirstdataCell.End(xlDown)
LastDataCell.Resize(10, 14).ClearContents
LastDataCell.Resize(7, 14).Delete Shift:=xlUp

'reset Lastdatacell since it was deleted
Set LastDataCell = FirstdataCell.End(xlDown)
Set TotalCell = LastDataCell.Offset(2, 0)
TotalCell.Formula = "=SUM(" & FirstdataCell.Address & ":" & _
LastDataCell.Address & ")"
'copy formula across row
TotalCell.Copy _
Destination:=Range(TotalCell, TotalCell.Offset(0, 14))

"Rick" wrote:

I download a report into Excel and I have created a macro to reformat it so
it is easy to read.

I want to add totals at the bottom of each section break. The problem I have
is that I don't know how big each section is as it changes from month to
month. when I add the section breaks I use;

ActiveCell.Offset(2, 0).End(xlDown).Resize(10, 14).Select
Selection.ClearContents
ActiveCell.Resize(7, 14).Select
Selection.Delete Shift:=xlUp

Which takes the cells I don't want and deletes and clears them out leaving 3
blank lines.

There are several of these sections of various lengths and I want to add
total to the bottom of each of them, there are 6 columns in each section.

How do I get the formula to add all cells above up to the first blank cell?

Thanks in advance
Rick

Add sum to dynamic range in Macro
 

Thanks Joel.

So for each section total do I just put this code where each section is
created?

Thanks again in advance

"Joel" wrote:

It is always better to use intermediate variables that are descriptive so you
can document the code easily.

Set HeaderCell = ActiveCell
Set FirstdataCell = HeaderCell.Offset(2, 0)
Set LastDataCell = FirstdataCell.End(xlDown)
LastDataCell.Resize(10, 14).ClearContents
LastDataCell.Resize(7, 14).Delete Shift:=xlUp

'reset Lastdatacell since it was deleted
Set LastDataCell = FirstdataCell.End(xlDown)
Set TotalCell = LastDataCell.Offset(2, 0)
TotalCell.Formula = "=SUM(" & FirstdataCell.Address & ":" & _
LastDataCell.Address & ")"
'copy formula across row
TotalCell.Copy _
Destination:=Range(TotalCell, TotalCell.Offset(0, 14))

"Rick" wrote:

I download a report into Excel and I have created a macro to reformat it so
it is easy to read.

I want to add totals at the bottom of each section break. The problem I have
is that I don't know how big each section is as it changes from month to
month. when I add the section breaks I use;

ActiveCell.Offset(2, 0).End(xlDown).Resize(10, 14).Select
Selection.ClearContents
ActiveCell.Resize(7, 14).Select
Selection.Delete Shift:=xlUp

Which takes the cells I don't want and deletes and clears them out leaving 3
blank lines.

There are several of these sections of various lengths and I want to add
total to the bottom of each of them, there are 6 columns in each section.

How do I get the formula to add all cells above up to the first blank cell?

Thanks in advance
Rick

Add sum to dynamic range in Macro
 

I based this code on your original code that was using the ACTIVECELL. There
are better way of doing this if you have multiple sections of code on the
same worksheet.

It seem you have header rows seperated by one blank row and the rows of data
followed by at least one blank row. If there is some pattern to your
worksheet of a clear way of identifying the heders rows one macro can be
writen to transverse the entire worksheet.

You could have something as simple as this

Header Row
one blank row
Data cells
one or more blank rows

Header Row
one blank row
Data cells
one or more blank rows

repeat down the worksheet

"Rick" wrote:

Thanks Joel.

So for each section total do I just put this code where each section is
created?

Thanks again in advance

"Joel" wrote:

It is always better to use intermediate variables that are descriptive so you
can document the code easily.

Set HeaderCell = ActiveCell
Set FirstdataCell = HeaderCell.Offset(2, 0)
Set LastDataCell = FirstdataCell.End(xlDown)
LastDataCell.Resize(10, 14).ClearContents
LastDataCell.Resize(7, 14).Delete Shift:=xlUp

'reset Lastdatacell since it was deleted
Set LastDataCell = FirstdataCell.End(xlDown)
Set TotalCell = LastDataCell.Offset(2, 0)
TotalCell.Formula = "=SUM(" & FirstdataCell.Address & ":" & _
LastDataCell.Address & ")"
'copy formula across row
TotalCell.Copy _
Destination:=Range(TotalCell, TotalCell.Offset(0, 14))

"Rick" wrote:

I download a report into Excel and I have created a macro to reformat it so
it is easy to read.

I want to add totals at the bottom of each section break. The problem I have
is that I don't know how big each section is as it changes from month to
month. when I add the section breaks I use;

ActiveCell.Offset(2, 0).End(xlDown).Resize(10, 14).Select
Selection.ClearContents
ActiveCell.Resize(7, 14).Select
Selection.Delete Shift:=xlUp

Which takes the cells I don't want and deletes and clears them out leaving 3
blank lines.

There are several of these sections of various lengths and I want to add
total to the bottom of each of them, there are 6 columns in each section.

How do I get the formula to add all cells above up to the first blank cell?

Thanks in advance
Rick

Add sum to dynamic range in Macro
 

Joel,

I have just inserted this into the macro and it worked great except when it
copies the formula across the columns they all have the first column's
reference rather than the column they are in.

How do I fix this?

Thanks
Rick


"Joel" wrote:

I based this code on your original code that was using the ACTIVECELL. There
are better way of doing this if you have multiple sections of code on the
same worksheet.

It seem you have header rows seperated by one blank row and the rows of data
followed by at least one blank row. If there is some pattern to your
worksheet of a clear way of identifying the heders rows one macro can be
writen to transverse the entire worksheet.

You could have something as simple as this

Header Row
one blank row
Data cells
one or more blank rows

Header Row
one blank row
Data cells
one or more blank rows

repeat down the worksheet

"Rick" wrote:

Thanks Joel.

So for each section total do I just put this code where each section is
created?

Thanks again in advance

"Joel" wrote:

It is always better to use intermediate variables that are descriptive so you
can document the code easily.

Set HeaderCell = ActiveCell
Set FirstdataCell = HeaderCell.Offset(2, 0)
Set LastDataCell = FirstdataCell.End(xlDown)
LastDataCell.Resize(10, 14).ClearContents
LastDataCell.Resize(7, 14).Delete Shift:=xlUp

'reset Lastdatacell since it was deleted
Set LastDataCell = FirstdataCell.End(xlDown)
Set TotalCell = LastDataCell.Offset(2, 0)
TotalCell.Formula = "=SUM(" & FirstdataCell.Address & ":" & _
LastDataCell.Address & ")"
'copy formula across row
TotalCell.Copy _
Destination:=Range(TotalCell, TotalCell.Offset(0, 14))

"Rick" wrote:

I download a report into Excel and I have created a macro to reformat it so
it is easy to read.

I want to add totals at the bottom of each section break. The problem I have
is that I don't know how big each section is as it changes from month to
month. when I add the section breaks I use;

ActiveCell.Offset(2, 0).End(xlDown).Resize(10, 14).Select
Selection.ClearContents
ActiveCell.Resize(7, 14).Select
Selection.Delete Shift:=xlUp

Which takes the cells I don't want and deletes and clears them out leaving 3
blank lines.

There are several of these sections of various lengths and I want to add
total to the bottom of each of them, there are 6 columns in each section.

How do I get the formula to add all cells above up to the first blank cell?

Thanks in advance
Rick

Add sum to dynamic range in Macro
 

The line below is corrected. Using Address returns $A$1. adding
columnAbosolute returns A$1.

TotalCell.Formula = "=SUM(" & _
FirstdataCell.Address(columnAbsolute:=False) & ":" & _
LastdataCell.Address(columnAbsolute:=False) & ")"

"Rick" wrote:

Joel,

I have just inserted this into the macro and it worked great except when it
copies the formula across the columns they all have the first column's
reference rather than the column they are in.

How do I fix this?

Thanks
Rick


"Joel" wrote:

I based this code on your original code that was using the ACTIVECELL. There
are better way of doing this if you have multiple sections of code on the
same worksheet.

It seem you have header rows seperated by one blank row and the rows of data
followed by at least one blank row. If there is some pattern to your
worksheet of a clear way of identifying the heders rows one macro can be
writen to transverse the entire worksheet.

You could have something as simple as this

Header Row
one blank row
Data cells
one or more blank rows

Header Row
one blank row
Data cells
one or more blank rows

repeat down the worksheet

"Rick" wrote:

Thanks Joel.

So for each section total do I just put this code where each section is
created?

Thanks again in advance

"Joel" wrote:

It is always better to use intermediate variables that are descriptive so you
can document the code easily.

Set HeaderCell = ActiveCell
Set FirstdataCell = HeaderCell.Offset(2, 0)
Set LastDataCell = FirstdataCell.End(xlDown)
LastDataCell.Resize(10, 14).ClearContents
LastDataCell.Resize(7, 14).Delete Shift:=xlUp

'reset Lastdatacell since it was deleted
Set LastDataCell = FirstdataCell.End(xlDown)
Set TotalCell = LastDataCell.Offset(2, 0)
TotalCell.Formula = "=SUM(" & FirstdataCell.Address & ":" & _
LastDataCell.Address & ")"
'copy formula across row
TotalCell.Copy _
Destination:=Range(TotalCell, TotalCell.Offset(0, 14))

"Rick" wrote:

I download a report into Excel and I have created a macro to reformat it so
it is easy to read.

I want to add totals at the bottom of each section break. The problem I have
is that I don't know how big each section is as it changes from month to
month. when I add the section breaks I use;

ActiveCell.Offset(2, 0).End(xlDown).Resize(10, 14).Select
Selection.ClearContents
ActiveCell.Resize(7, 14).Select
Selection.Delete Shift:=xlUp

Which takes the cells I don't want and deletes and clears them out leaving 3
blank lines.

There are several of these sections of various lengths and I want to add
total to the bottom of each of them, there are 6 columns in each section.

How do I get the formula to add all cells above up to the first blank cell?

Thanks in advance
Rick

Add sum to dynamic range in Macro
 

just another way:

LastdataCell.Address(,0)

--


Gary


"Joel" wrote in message
...
The line below is corrected. Using Address returns $A$1. adding
columnAbosolute returns A$1.

TotalCell.Formula = "=SUM(" & _
FirstdataCell.Address(columnAbsolute:=False) & ":" & _
LastdataCell.Address(columnAbsolute:=False) & ")"

"Rick" wrote:

Joel,

I have just inserted this into the macro and it worked great except when it
copies the formula across the columns they all have the first column's
reference rather than the column they are in.

How do I fix this?

Thanks
Rick


"Joel" wrote:

I based this code on your original code that was using the ACTIVECELL.
There
are better way of doing this if you have multiple sections of code on the
same worksheet.

It seem you have header rows seperated by one blank row and the rows of
data
followed by at least one blank row. If there is some pattern to your
worksheet of a clear way of identifying the heders rows one macro can be
writen to transverse the entire worksheet.

You could have something as simple as this

Header Row
one blank row
Data cells
one or more blank rows

Header Row
one blank row
Data cells
one or more blank rows

repeat down the worksheet

"Rick" wrote:

Thanks Joel.

So for each section total do I just put this code where each section is
created?

Thanks again in advance

"Joel" wrote:

It is always better to use intermediate variables that are descriptive
so you
can document the code easily.

Set HeaderCell = ActiveCell
Set FirstdataCell = HeaderCell.Offset(2, 0)
Set LastDataCell = FirstdataCell.End(xlDown)
LastDataCell.Resize(10, 14).ClearContents
LastDataCell.Resize(7, 14).Delete Shift:=xlUp

'reset Lastdatacell since it was deleted
Set LastDataCell = FirstdataCell.End(xlDown)
Set TotalCell = LastDataCell.Offset(2, 0)
TotalCell.Formula = "=SUM(" & FirstdataCell.Address & ":" & _
LastDataCell.Address & ")"
'copy formula across row
TotalCell.Copy _
Destination:=Range(TotalCell, TotalCell.Offset(0, 14))

"Rick" wrote:

I download a report into Excel and I have created a macro to reformat
it so
it is easy to read.

I want to add totals at the bottom of each section break. The problem
I have
is that I don't know how big each section is as it changes from month
to
month. when I add the section breaks I use;

ActiveCell.Offset(2, 0).End(xlDown).Resize(10, 14).Select
Selection.ClearContents
ActiveCell.Resize(7, 14).Select
Selection.Delete Shift:=xlUp

Which takes the cells I don't want and deletes and clears them out
leaving 3
blank lines.

There are several of these sections of various lengths and I want to
add
total to the bottom of each of them, there are 6 columns in each
section.

How do I get the formula to add all cells above up to the first blank
cell?

Thanks in advance
Rick

Add sum to dynamic range in Macro
 

Gary, how many people know what (,0) really means. It doesn't cost anything
to add the columnAbsolute:=False and it makes the code easier to maintain. I
guess you never took any real courses in programming.

"Gary Keramidas" wrote:

just another way:

LastdataCell.Address(,0)

--


Gary


"Joel" wrote in message
...
The line below is corrected. Using Address returns $A$1. adding
columnAbosolute returns A$1.

TotalCell.Formula = "=SUM(" & _
FirstdataCell.Address(columnAbsolute:=False) & ":" & _
LastdataCell.Address(columnAbsolute:=False) & ")"

"Rick" wrote:

Joel,

I have just inserted this into the macro and it worked great except when it
copies the formula across the columns they all have the first column's
reference rather than the column they are in.

How do I fix this?

Thanks
Rick


"Joel" wrote:

I based this code on your original code that was using the ACTIVECELL.
There
are better way of doing this if you have multiple sections of code on the
same worksheet.

It seem you have header rows seperated by one blank row and the rows of
data
followed by at least one blank row. If there is some pattern to your
worksheet of a clear way of identifying the heders rows one macro can be
writen to transverse the entire worksheet.

You could have something as simple as this

Header Row
one blank row
Data cells
one or more blank rows

Header Row
one blank row
Data cells
one or more blank rows

repeat down the worksheet

"Rick" wrote:

Thanks Joel.

So for each section total do I just put this code where each section is
created?

Thanks again in advance

"Joel" wrote:

It is always better to use intermediate variables that are descriptive
so you
can document the code easily.

Set HeaderCell = ActiveCell
Set FirstdataCell = HeaderCell.Offset(2, 0)
Set LastDataCell = FirstdataCell.End(xlDown)
LastDataCell.Resize(10, 14).ClearContents
LastDataCell.Resize(7, 14).Delete Shift:=xlUp

'reset Lastdatacell since it was deleted
Set LastDataCell = FirstdataCell.End(xlDown)
Set TotalCell = LastDataCell.Offset(2, 0)
TotalCell.Formula = "=SUM(" & FirstdataCell.Address & ":" & _
LastDataCell.Address & ")"
'copy formula across row
TotalCell.Copy _
Destination:=Range(TotalCell, TotalCell.Offset(0, 14))

"Rick" wrote:

I download a report into Excel and I have created a macro to reformat
it so
it is easy to read.

I want to add totals at the bottom of each section break. The problem
I have
is that I don't know how big each section is as it changes from month
to
month. when I add the section breaks I use;

ActiveCell.Offset(2, 0).End(xlDown).Resize(10, 14).Select
Selection.ClearContents
ActiveCell.Resize(7, 14).Select
Selection.Delete Shift:=xlUp

Which takes the cells I don't want and deletes and clears them out
leaving 3
blank lines.

There are several of these sections of various lengths and I want to
add
total to the bottom of each of them, there are 6 columns in each
section.

How do I get the formula to add all cells above up to the first blank
cell?

Thanks in advance
Rick