1

I have a range of data in cells BA8 through and including BN10 in worksheet "HR Eval Report" in the active workbook.

I want to sort this range in ascending order based upon the values in cells BC8:BC10. Here is the part of my code causing problems:

Dim rngOutData As Range
...
ActiveWorkbook.Worksheets("HR Eval Report").Range("BA8").Select
Set rngOutData = ActiveWorkbook.Worksheets("HR Eval Report").Range(Selection, _
Selection.End(xlToRight)).Select

The Set rngOutData statement throws the error. I have tried fully qualifying the range as similar 424 problem answers indicated that as a solution, but it didn't solve the problem. Any advice greatly appreciated.

Community
  • 1
  • 1
GeorgeInNC
  • 35
  • 1
  • 9

3 Answers3

1

You need to declare your objects and work with it. Avoid the use of selection. You may want to see This

Here is a simple way to sort your range ascending on BC8

Dim rngOutData As Range
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("HR Eval Report")

Set rngOutData = ws.Range("BA8:BN10")

With rngOutData
    .Sort Key1:=ws.Range("BC8"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
End With

Note: If your data doesn't have headers then change Header:=xlYes to Header:=xlNo as suggested correctly by @Jeeped in the comments below.

Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • With only three rows to sort, do you think there is a header? The OP may need `, Header:=xlNo` . `Key1:=ws.Range("BA8")` might have to be `Key1:=.Columns(3)` . –  May 21 '15 at 21:27
  • @Jeeped: I don't know the answer to that question as OP as not mentioned anything :) However, it is a good point. I will mention that in the above post. – Siddharth Rout May 21 '15 at 21:29
  • @Jeeped: Mentioned that in the post. Regarding your recent edit regarding the key, what I mentioned is correct. – Siddharth Rout May 21 '15 at 21:32
  • I thought the OP had stated *'ascending order based upon the values in cells BC8:BC10'*. –  May 21 '15 at 21:53
  • @Jeeped: Ah I see what you mean. I misread it. in that case wouldn't `Key1:=ws.Range("BC8")` suffice? – Siddharth Rout May 21 '15 at 21:56
  • Sure, I was just using the method that I generally use when coding a `.Cells.Sort` within a With/End With (third column). Sorry for being obtuse. –  May 21 '15 at 22:00
  • @Jeeped: No you are not being obtuse :) You picked up an obvious thing that I missed. I thank you for bringing that to my notice. – Siddharth Rout May 21 '15 at 22:01
0

The reason your code is throwing an error is that you are trying to assign the return value of Range.Select to an object variable. However, the Select method doesn't return an object, it returns True (if the range was successfully selected).

There are two ways to achieve exactly what your code is trying to do: select the range and then assign the selection to the object variable:

ActiveWorkbook.Worksheets("HR Eval Report").Range("BA8").Select
ActiveWorkbook.Worksheets("HR Eval Report").Range(Selection, _
    Selection.End(xlToRight)).Select
Set rngOutData = Selection

or assign the range to the variable and then select that:

ActiveWorkbook.Worksheets("HR Eval Report").Range("BA8").Select
Set rngOutData = ActiveWorkbook.Worksheets("HR Eval Report").Range(Selection, _
    Selection.End(xlToRight))
rngOutData.Select

However almost certainly there are much better ways to achieve the overall objective of sorting the cells, and you should generally avoid the use of Select (see this question for more). To get the same range into rngOutData for instance, the following is faster, more robust and easier to read:

With ActiveWorkbook.Worksheets("HR Eval Report")
    Set rngOutData = .Range(.Range("BA8"), .Range("BA8").End(xlToRight))
End With

A similar thing applies to ActiveWorkbook - generally it would be better to have a variable containing a reference to the workbook and use that.

Community
  • 1
  • 1
aucuparia
  • 2,021
  • 20
  • 27
-1

Try with this one, just follow the ' IF YOU... Step

Sub order()

Sheets("HR Eval Report").Select
Range("BC8").Select
ActiveWorkbook.Worksheets("HR Eval Report").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("HR Eval Report").Sort.SortFields.Add Key:=Range("BC8"), _
    SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("HR Eval Report").Sort
    .SetRange Range("BA8:BN10")
    .Header = xlNo ' IF YOU HAVE TITLE ON THE COLUMN THEN SET .Header x1Yes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

Luis Guicho
  • 5
  • 4