How do I use the .Cells property to refer to ActiveCell?

C

Caeres

I need to refer to the active cell using the Cells property (eg
Range.Cells(...)). How do I do this?
 
J

Jim Thomlinson

Your request does not really make a lot of sense??? The active cell is a
single cell on the active sheet. Are you trying to define a range based off
of the active cell???

MsgBox Cells(ActiveCell.Row, ActiveCell.Column).Address
MsgBox Cells(ActiveCell.Row + 5, ActiveCell.Column).Address
MsgBox ActiveCell.Offset(5, 0).Address
MsgBox ActiveCell.Resize(6, 1).Address
 
T

Thomas [PBD]

Caeres,

The way to do this would be to place the following code into the script.

Cells(ActiveCell.Row, ActiveCell.Column)
 
D

Dave Peterson

maybe...

dim myRng as range
with activecell
set myrng = .parent.range(.cells(.row,.column).address)
end with

But that seems very convoluted to me. Why can't you just use Activecell?
 
J

Jim Thomlinson

I just saw your other post. Try to stick 1 post to make life easy...

range(ActiveCell, range("N3")).Select

Selects a rectangle from the active cell through N3...
 
C

Caeres

Here's the end goal. I'm trying to sort a range from the active cell to N3.
Here's the code I've got right now:

ActiveCell.Select
With ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort
.SortFields.Clear
.SortFields.Add Key:=Range(Cells(ActiveCell.Row, ActiveCell.Column),
Cells(3, 14)), _
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
.SetRange Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(3,
14))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

and I get an error message:
"Run-time error '1004': The sort reference is not valid. Make sure that
it's within the data you want to sort, and the first Sort By box isn't the
same or blank."

with the ".SetRange" line highlighted in the debugger. Any ideas? Thanks
for the initial help, that was what I was looking for. Just turns out it
wasn't my only problem.
 
T

TomPl

Once you have identified the range you need to decide what column(s) you want
to sort on. Do you know?

Tom
 
C

Caeres

Yes, I want to sort based on column A.

TomPl said:
Once you have identified the range you need to decide what column(s) you want
to sort on. Do you know?

Tom
Click to expand...
 
T

TomPl

I'm not sure the point to all of this, but the following should do what you
have described. This will sort the data in columns A thru N based on the
value in column A. Only rows 4 thru the row of the active cell will be
sorted.

Sub SortSelection()

Dim rngCell As String

rngCell = ActiveCell.Row

ActiveSheet.Range("A3:N" & rngCell).Sort Key1:=Range("a4"),
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveSheet.Range("A" & rngCell).Select

End Sub

Hope'n this works.

Tom
 
C

Caeres

Had a few problems with your code, but you answered my question. I was
inputting the key value wrong. Thanks for the help.
 
Top