How do I return the location (as a range) of ActiveCell?

[Caeres]

I need the reference to a range to be dynamic, based on the ActiveCell. How
can I return the range of the ActiveCell so that I can use it in operations
requiring a Range as an argument?

 

[Don Guillett]

?
ma=activecell.address

 

[Caeres]

Thanks, just didn't know the syntax. I appreciate it.

 

[Caeres]

Okay, scratch that, I'm still making some mistake. Here's the code I've got:

MsgBox ActiveCell.Address
Dim ActiveLoc As Range
ActiveLoc = ActiveCell.Address
[ActiveRange:N3].Select
ActiveRange.Activate
ActiveWorkbook.Worksheets("D-11 O.F.").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("D-11 O.F.").Sort.SortFields.Add
Key:=Range("ActiveRange"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("D-11 O.F.").Sort
.SetRange Selection.Range
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

The MsgBox line returns $A$6, so I know .Address is the right syntax, but
after the message box I get an error message: "Run-time error '91': Object
variable or With block variable not set" and the third line is highlighted in
the debugger. Hovering over it, I get the bubbles "ActiveLoc = Nothing" and
"ActiveCell.Address = $A$6". So something's going wrong with the assignment
of the range value to the variable, but I don't know why. Help?

 

[Gord Dibben]

Dim ActiveLoc As String

I don't know what the next line does but the above change will get you
there.


Gord Dibben MS Excel MVP