Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting range in macro (Excel 2003)

    Selection.Sort Key1:=Range(ActiveCell.Offset(Count(Rows(xlDown)), 50), Order1:=xlAscending, Header:=xlGuess _
    , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal)

    The above is an attempt to select, from the active cell, a range from it down to the last cell in its column and x columns (now 50) to the right, and then to sort on the Column of the active cell (Column A in all cases, with the date) in ascending order. Of curse, it does not work. It is a real pain to get VBA to select a range. Besides the confusion as to whether you have to work with a colletion, a method or a property, I can never get any rational help from that VBA help in Excel. You must know the answer before you ask it. The De###### stops at the Count (rows.... where I am trying to define the row range as from the active cell down to the last row in its column. I give up. Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Selecting range in macro (Excel 2003)

    There is some confusion here - instead of expanding the range to sort, you are trying to expand the sort key. That makes no sense.

    Do you really need to specify the range to sort explicitly? In many cases, it is sufficient to specify a cell in the range to be sorted; Excel will automatically determine the range. If you do need to set the range, you can use something like this:

    Range(ActiveCell, ActiveCell.End(xlDown).Offset(0, 50)).Sort Key1:=ActiveCell, Header:=xlGuess

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting range in macro (Excel 2003)

    That worked fine. Thank you. The range changes each time because I'm sorting a checkbook where present and future items have been entered, so at any column A cell I want to say "Sort ascending from here on down and 50 columns to the right" to rearrange things by advancing date. I see that the code is more efficient than what I was trying: to emulate the way I do it manually, while you do it in one line with the sort parameters as a property (?) of the range...I would have never thought of it that way. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •