Range problem

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
 
B

Bernie Deitrick

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
 
W

wotrac

Thanks for the reply, but what if my range starts at row D11
in all the relevant columns.
 
W

wotrac

Thanks for the reply, but what if my range starts at row D11
in all the relevant columns.
 
B

Bernie Deitrick

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
 
Top