Subtotals in a range that could vary in size
 

I've been thinking about this for a while and have had no luck. I need to
subtotal items that are in a column. The column always begins at B5 but it
could be of any length. I have tried to determine the end of the column with
two approaches:

1. Find the first blank at the end of the column and use the row value of
that cell to set the range;
2. Or, just look at all the cells in column B with
Range("B65536").End(xlUp).Row.

Neither has worked. I figure once I can set the row, I can separate out and
subtotal the items by counting the unique items and making a loop through the
range. Of course, there is probably an easier way. I'm just running out of
idea.

Any help offered will be greatly appreciated. Thank-you very much.

Yobrokerboy.

Subtotals in a range that could vary in size
 

To get the last used row

dim last row as long
lastrow = cells(row.count,2).end(xlup).row

(the 2 above refers to column 2, change as required or use the column letter
as "B")

but... why not use subtotals - it is much simpler than writing the code

--
Cheers
Nigel



"yobrokerboy" wrote in message
...
I've been thinking about this for a while and have had no luck. I need to
subtotal items that are in a column. The column always begins at B5 but it
could be of any length. I have tried to determine the end of the column
with
two approaches:

1. Find the first blank at the end of the column and use the row value of
that cell to set the range;
2. Or, just look at all the cells in column B with
Range("B65536").End(xlUp).Row.

Neither has worked. I figure once I can set the row, I can separate out
and
subtotal the items by counting the unique items and making a loop through
the
range. Of course, there is probably an easier way. I'm just running out of
idea.

Any help offered will be greatly appreciated. Thank-you very much.

Yobrokerboy.