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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.