Adding totals to columns where number of rows varies
I have a report that is exported into Excel from another application, and I
am attempting to write a macro to make some formatting changes. The number of rows of data changes depending upon the report parameters. Data appears in columns from A to H, and I want totals at the bottom of columns G and H only. The following code that I got off of this site does almost what I need, except that I end up with totals in more than just the two columns that I want. How do I limit the range so that I get totals only in G and H? Set rng = ActiveSheet.UsedRange Set rng = rng.Rows(rng.Rows.Count).Offset(2, 0).Cells rng.FormulaR1C1 = "=Sum(R[-1]C:R1C)" |
Adding totals to columns where number of rows varies
Perfect! Thanks!
"JLGWhiz" wrote: Sub totColGH() lr = Cells(Rows.Count, 7).End(xlUp).Row Set rng = ActiveSheet.Range(Cells(1, 7), Cells(lr, 8)) Set rng = rng.Rows(rng.Rows.Count).Offset(2, 0).Cells rng.FormulaR1C1 = "=Sum(R[-1]C:R1C)" End Sub "Rod from Corrections" wrote: I have a report that is exported into Excel from another application, and I am attempting to write a macro to make some formatting changes. The number of rows of data changes depending upon the report parameters. Data appears in columns from A to H, and I want totals at the bottom of columns G and H only. The following code that I got off of this site does almost what I need, except that I end up with totals in more than just the two columns that I want. How do I limit the range so that I get totals only in G and H? Set rng = ActiveSheet.UsedRange Set rng = rng.Rows(rng.Rows.Count).Offset(2, 0).Cells rng.FormulaR1C1 = "=Sum(R[-1]C:R1C)" |
Adding totals to columns where number of rows varies
Try:
Set rng = ActiveSheet.UsedRange Set rng = rng.Rows(rng.Rows.Count).Offset(2, 6).Resize(1, 2).Cells rng.FormulaR1C1 = "=Sum(R[-1]C:R1C)" Regards Trevor "Rod from Corrections" wrote in message ... I have a report that is exported into Excel from another application, and I am attempting to write a macro to make some formatting changes. The number of rows of data changes depending upon the report parameters. Data appears in columns from A to H, and I want totals at the bottom of columns G and H only. The following code that I got off of this site does almost what I need, except that I end up with totals in more than just the two columns that I want. How do I limit the range so that I get totals only in G and H? Set rng = ActiveSheet.UsedRange Set rng = rng.Rows(rng.Rows.Count).Offset(2, 0).Cells rng.FormulaR1C1 = "=Sum(R[-1]C:R1C)" |
Adding totals to columns where number of rows varies
Sub totColGH()
lr = Cells(Rows.Count, 7).End(xlUp).Row Set rng = ActiveSheet.Range(Cells(1, 7), Cells(lr, 8)) Set rng = rng.Rows(rng.Rows.Count).Offset(2, 0).Cells rng.FormulaR1C1 = "=Sum(R[-1]C:R1C)" End Sub "Rod from Corrections" wrote: I have a report that is exported into Excel from another application, and I am attempting to write a macro to make some formatting changes. The number of rows of data changes depending upon the report parameters. Data appears in columns from A to H, and I want totals at the bottom of columns G and H only. The following code that I got off of this site does almost what I need, except that I end up with totals in more than just the two columns that I want. How do I limit the range so that I get totals only in G and H? Set rng = ActiveSheet.UsedRange Set rng = rng.Rows(rng.Rows.Count).Offset(2, 0).Cells rng.FormulaR1C1 = "=Sum(R[-1]C:R1C)" |
Adding totals to columns where number of rows varies
That worked, too! Thanks, Trevor.
"Trevor Shuttleworth" wrote: Try: Set rng = ActiveSheet.UsedRange Set rng = rng.Rows(rng.Rows.Count).Offset(2, 6).Resize(1, 2).Cells rng.FormulaR1C1 = "=Sum(R[-1]C:R1C)" Regards Trevor "Rod from Corrections" wrote in message ... I have a report that is exported into Excel from another application, and I am attempting to write a macro to make some formatting changes. The number of rows of data changes depending upon the report parameters. Data appears in columns from A to H, and I want totals at the bottom of columns G and H only. The following code that I got off of this site does almost what I need, except that I end up with totals in more than just the two columns that I want. How do I limit the range so that I get totals only in G and H? Set rng = ActiveSheet.UsedRange Set rng = rng.Rows(rng.Rows.Count).Offset(2, 0).Cells rng.FormulaR1C1 = "=Sum(R[-1]C:R1C)" |
All times are GMT +1. The time now is 10:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com