Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range Name Macro | Excel Discussion (Misc queries) | |||
dynamic range in a macro | Excel Programming | |||
dynamic range based on criteria, within a dynamic range, passed to a function | Excel Programming | |||
creating a dynamic range based on criteria, within a dynamic range, and passing it to a function | Excel Programming | |||
Dynamic range for autofill macro | Excel Discussion (Misc queries) |