Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Hatsukaichi, Hiroshima, Japan
    Posts
    904
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting A selection using VBA (Excel 97)

    Hello,

    I have a macro to select 80 rows in the column of the active cell:

    Cells(2, ActiveCell.Column).Select
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(78, 0)).Select

    What I'd like to be able to do is to add code to sort the selection alphabetically. Currently I'm clicking on the sort ascending button but I'd like to automate this step. One thing is that I only want to sort the selected cells I don't want any other columns sorted when sorting the selection. Each column of data in my spreadsheet is independent. I tried:

    With ActiveCell. CurrentRegion

    .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    End With

    But this seemed to sort everything. Is there any simple way to do what I want?

    Thanks,

    Chris (Hunt)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sorting A selection using VBA (Excel 97)

    How about:

    with Range(Cells(2, ActiveCell.Column), cells(80,activecell.column)).
    .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With

    Steve

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

    Re: Sorting A selection using VBA (Excel 97)

    Instead of

    With ActiveCell.CurrentRegion

    use

    With Selection

    This will limit the sort operation to the selected column.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Hatsukaichi, Hiroshima, Japan
    Posts
    904
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting A selection using VBA (Excel 97)

    Thank you both for your very swift replies.

    Best wishes,

    Chris

Posting Permissions

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