0

I want to order a column which will be at varying positions each time the macro is run. Here is my current code for completing the action:

Range(ActiveCell, ActiveCell.Offset(1000, 1)).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(ActiveCell), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range(ActiveCell, ActiveCell(1000, 1))
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
     End With

The reason for offsetting by 1000 is that I do not know how many entries there will be in the column, but I know it will be less than 1000... the offset by 1 it to order the numerical values (which are in that 1 column offset) and their company name identifiers, in the other column. Not sure how to fix the code, but in debugging, I get an error specifically after trying to run:

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(ActiveCell), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Any ideas?

Runeaway3
  • 1,439
  • 1
  • 17
  • 43
  • Reading [this](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) should help your code be less buggy. – Kyle Jul 05 '16 at 17:24
  • Please also provide the error message you get – arcadeprecinct Jul 05 '16 at 17:28
  • The error I get is: "Run-time error '1004': application-defined or object-defined error" – Runeaway3 Jul 05 '16 at 17:30
  • @Kyle I have seen alot of people suggesting not to use select and activecell... why is that? It works fine for me when I can figure out the syntax errors I have – Runeaway3 Jul 05 '16 at 17:35
  • 1
    As your programs get more complex, it is a lot harder to ensure the sheet, workbook, cell, etc that you are selecting is actually the one you want. It is much better (and easier to read the code) when you fully qualify `Workbooks("workbook name").Sheets("Sheet name").Range("a1:a1000").Sort.Sortfields.Clear`. It will also save you lines of code...which I think is good. – Kyle Jul 05 '16 at 17:38
  • @Kyle two other good reasons: Selecting is very slow (can be improved by disabling screenupdating though), using many selections also can lead to runtime errors even when there's nothing wrong with the code (I think). – arcadeprecinct Jul 05 '16 at 17:44

1 Answers1

3

Range(ActiveCell) does not work because ActiveCell is a range itself. So just use ActiveCell instead of Range(ActiveCell). Better yet, avoid Active... and .Select as Kyle suggested

arcadeprecinct
  • 3,767
  • 1
  • 12
  • 18