MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Method 'Range' of object '_Global' failed when sorting

  Asked By: Josefina    Date: Sep 04    Category: MS Office    Views: 1615

I am having a bit of trouble using the sort method. I am attempting to
specify the Key1 and Key2 using the Cells() references. I get the 1004
error when I run it though. What is it that I am doing wrong?

cCol = ActiveCell.Column
rRow = ActiveCell.Row 'get row number for beginning of range

rEnd = Cells(Rows.Count, 3).End(xlUp).Row
Cells(1, 1).Select
cCol = ActiveCell.Column

Range(Cells(rRow + 1, 1), Cells(rEnd, cCol)).Select

Selection.Sort Key1:=Range(Cells(1, cCol)), Order1:=xlAscending,
Key2:=Range( _
Cells(1, cCol - 1)), Order2:=xlAscending, Header:=xlNo,
OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _



1 Answer Found

Answer #1    Answered By: Laaibah Malik     Answered On: Sep 04

I think you'll find that Range(Cells(1, cCol)), etc, is bad syntax. The
one-argument version of range  expects a string with a range reference in it.

Just remove the Range() from around the Cells(...), e.g.

Range(Cells(1, cCol))

should be

Cells(1, cCol)

or (perversely)

Range(Cells(1, cCol).Address)

Note that the two parameter version of Range - Range(Cells(rRow + 1, 1),
Cells(rEnd, cCol)).Select - is fine.

Didn't find what you were looking for? Find more on Method 'Range' of object '_Global' failed when sorting Or get search suggestion and latest updates.