getting address values of a named range? 1

[eHanSolo]

hi there,

I have a named range of which i'd like to get the row part of the adress of the first and last value of the range (the range is quite large and only a portion of that range has values in it)....

can someone point me in the right direction in doing this in VBA please??

many many thanks,

e

 

[PHV]

You may try something like this:
Dim a
a = Split([yourRange].Address, "$")
MsgBox "First row=" & a(1) & ", last row=" & a(3)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

PHV - "only a portion of that range has values in it"

With [YourRange]
    FirstRow = .Cells(1, 1).End(xlDown).Row
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

OOps, misunderstood the issue ...

 

[eHanSolo]

works really well.. thank you!!!

 

[firefytr]

Geoff, what if there is a value in the top/bottom cell of the range? You will have skewed results..

-----------
Regards,
Zack Barresse

 

[xlbo]

Zack - true - amendment:

With [YourRange]
    If IsEmpty(.Cells(1, 1)) Then
      FirstRow = .Cells(1, 1).End(xlDown).Row
    Else
      FirstRow = .Cells(1, 1).Row
    End If

    If IsEmpty(.Cells(.Rows.Count, 1)) Then
      LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    Else
      LastRow = .Cells(.Rows.Count, 1).Row
    End If
End With

You may want to change the IsEmpty test for ="" if you want to find actual values as opposed to non empty cells..

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question