Thread: Return Range Address from Active Range

I know that I can return the address from the Active Cell by looking at the
Address property of the object:

ActiveCell.Address

How do I do this if the object is a range that is hightlighted?
ActiveRange.Address does not seem to work. There does not seem to be an
object called ActiveRange

Thanks

Hi Monkey.

Try:

Selection.Address

---
Regards,
Norman



"ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
news:2AED0E33-90BD-4654-ACC8-9A31A883728D@microsoft.com...
>I know that I can return the address from the Active Cell by looking at the
> Address property of the object:
>
> ActiveCell.Address
>
> How do I do this if the object is a range that is hightlighted?
> ActiveRange.Address does not seem to work. There does not seem to be an
> object called ActiveRange
>
> Thanks
>
>

How do I then wrap a function around Selection. I am trying to tell if the
Selection contains more than 1 cell. However when I try to pass the
Selection as a Range to the function I get a Type Mismatch Error

Sub CopyFormula()
Dim X As New DataObject
If SingleCell(Selection) = False Then
MsgBox ("Your range can only include 1 cell!")
Else
X.SetText ActiveCell.Formula
X.PutInClipboard
End If
End Sub
'**********************************************
Function SingleCell(X As Range)
If X.Rows > 1 Or X.Columns > 1 Then
SingleCell = False
Else
SingleCell = True
End If
End Function


Thanks

"Norman Jones" wrote:

> Hi Monkey.
>
> Try:
>
> Selection.Address
>
> ---
> Regards,
> Norman
>
>
>
> "ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
> news:2AED0E33-90BD-4654-ACC8-9A31A883728D@microsoft.com...
> >I know that I can return the address from the Active Cell by looking at the
> > Address property of the object:
> >
> > ActiveCell.Address
> >
> > How do I do this if the object is a range that is hightlighted?
> > ActiveRange.Address does not seem to work. There does not seem to be an
> > object called ActiveRange
> >
> > Thanks
> >
> >
>
>
>

Hi Monkey,

Drop the function and try:

If Selection.Count > 1 Then
Msgbox "Your range can only include 1 cell!"
Else
'Your code
End If


--
Regards,
Norman



"ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
news:72D75E04-3E2D-43D4-897E-C2940010DF4E@microsoft.com...
> How do I then wrap a function around Selection. I am trying to tell if
> the
> Selection contains more than 1 cell. However when I try to pass the
> Selection as a Range to the function I get a Type Mismatch Error
>
> Sub CopyFormula()
> Dim X As New DataObject
> If SingleCell(Selection) = False Then
> MsgBox ("Your range can only include 1 cell!")
> Else
> X.SetText ActiveCell.Formula
> X.PutInClipboard
> End If
> End Sub
> '**********************************************
> Function SingleCell(X As Range)
> If X.Rows > 1 Or X.Columns > 1 Then
> SingleCell = False
> Else
> SingleCell = True
> End If
> End Function
>
>
> Thanks
>
> "Norman Jones" wrote:
>
>> Hi Monkey.
>>
>> Try:
>>
>> Selection.Address
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
>> news:2AED0E33-90BD-4654-ACC8-9A31A883728D@microsoft.com...
>> >I know that I can return the address from the Active Cell by looking at
>> >the
>> > Address property of the object:
>> >
>> > ActiveCell.Address
>> >
>> > How do I do this if the object is a range that is hightlighted?
>> > ActiveRange.Address does not seem to work. There does not seem to be
>> > an
>> > object called ActiveRange
>> >
>> > Thanks
>> >
>> >
>>
>>
>>