Add sum to dynamic range in Macro

[Rick]

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

 

[Joel]

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]

Thanks Joel.

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

Thanks again in advance

 

[Joel]

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]

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]

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

 

[Gary Keramidas]

just another way:

LastdataCell.Address(,0)

 

[Joel]

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.