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

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

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" <(E-Mail Removed)> wrote
in message news2C3AE2C-1960-4E2F-9A79-(E-Mail Removed)...
>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)"
>

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

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" <(E-Mail Removed)> wrote
> in message news2C3AE2C-1960-4E2F-9A79-(E-Mail Removed)...
> >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)"
> >
>
>
>