Return the row numbers or a dynamic range in VBA

Hello, I have finally figured out how to create a dynamic range and it works
great.
Now I need to query this dynamic range to determine the row numbers of the
first and last rows of the range.

Example:

I have a dynamic range named Range_6. Being dynamic in nature, this range
changes frequently. What I need to know is how can I query this "Range_6"
range to retrieve only its beginning row number and ending row number?

If I have a dynamic range that equates to D11:D142 - using VBA, how can I
query Range_6 to get just the 11 and 142 part? I don't need to select the
range "Range(range_6).Select", just return the row numbers?

Hope that makes sense!!

Thanks
Jim

Jim,

The first row:
MsgBox Range("Range_6").Row

The last row:
MsgBox Range("Range_6").Cells(Range("Range_6").Cells.Count).Row

HTH,
Bernie
MS Excel MVP


"Jim" <.> wrote in message news:%[email protected]...
> Hello, I have finally figured out how to create a dynamic range and it works great.
> Now I need to query this dynamic range to determine the row numbers of the first and last rows of
> the range.
>
> Example:
>
> I have a dynamic range named Range_6. Being dynamic in nature, this range changes frequently.
> What I need to know is how can I query this "Range_6" range to retrieve only its beginning row
> number and ending row number?
>
> If I have a dynamic range that equates to D11:D142 - using VBA, how can I query Range_6 to get
> just the 11 and 142 part? I don't need to select the range "Range(range_6).Select", just return
> the row numbers?
>
> Hope that makes sense!!
>
> Thanks
> Jim
>
>

Set rng = Range("myRange")
FirstRow = rng.Row
lastRow = rng(rng.Count).Row


--
HTH

Bob Phillips

"Jim" <.> wrote in message news:%[email protected]...
> Hello, I have finally figured out how to create a dynamic range and it
works
> great.
> Now I need to query this dynamic range to determine the row numbers of the
> first and last rows of the range.
>
> Example:
>
> I have a dynamic range named Range_6. Being dynamic in nature, this range
> changes frequently. What I need to know is how can I query this "Range_6"
> range to retrieve only its beginning row number and ending row number?
>
> If I have a dynamic range that equates to D11:D142 - using VBA, how can I
> query Range_6 to get just the 11 and 142 part? I don't need to select the
> range "Range(range_6).Select", just return the row numbers?
>
> Hope that makes sense!!
>
> Thanks
> Jim
>
>

Outstanding!! Thanks Bob and Bernie, both your suggestions worked GREAT!
This was really busting my chops.

Thanks so much!!!

Jim



"Jim" <.> wrote in message news:%[email protected]...
> Hello, I have finally figured out how to create a dynamic range and it
> works great.
> Now I need to query this dynamic range to determine the row numbers of the
> first and last rows of the range.
>
> Example:
>
> I have a dynamic range named Range_6. Being dynamic in nature, this range
> changes frequently. What I need to know is how can I query this "Range_6"
> range to retrieve only its beginning row number and ending row number?
>
> If I have a dynamic range that equates to D11:D142 - using VBA, how can I
> query Range_6 to get just the 11 and 142 part? I don't need to select the
> range "Range(range_6).Select", just return the row numbers?
>
> Hope that makes sense!!
>
> Thanks
> Jim
>
>