Range problem

  • Thread starter Thread starter wotrac
  • Start date Start date
W

wotrac

Hi

This is probably very simple for you guys, but I am having problems.
I want to sum the figures in a column, where the data is being imported
via a query. As the number of rows changes when the data is refreshed,
the number of rows in the sum will also change.

The first bit it easy, but I then want to display the result of my
calculation 5 lines below the last row of data. As the number of rows
changes every refresh, how do I tie it into the original range.


Or should I be going about the whole thing differently
 
wotrac,

For example, to put a sum formula 5 lines below a variable length
column B, with the sum leaving off the top row from that column to
allow for a label:

Range("B65536").End(xlUp)(6).Formula = _
"=Sum(" & Range(Range("B65536").End(xlUp), _
Range("B65536").End(xlUp).End(xlUp)(2)).Address & ")"

HTH,
Bernie
MS Excel MVP
Posted directly to the MS public newsgroups
Not associated with Excel Forums
 
Thanks for the reply, but what if my range starts at row D11
in all the relevant columns.
 
Thanks for the reply, but what if my range starts at row D11
in all the relevant columns.
 
For whatever column the activecell is in:

Cells(65536, ActiveCell.Column).End(xlUp)(6).Formula = _
"=Sum(" & Range(Cells(65536, ActiveCell.Column).End(xlUp), _
Cells(65536, ActiveCell.Column).End(xlUp).End(xlUp)(2)).Address & ")"

This will work as long as at least two cells are filled in the active cell's
column, no matter the start row.

HTH,
Bernie
MS Excel MVP
 
Back
Top