Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: variable sort macro?

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    153
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm sure this question has been asked and answered before, but I haven't come up with the right search criteria.

    I have a database in Excel97:

    C/R A B C etc.
    1 name11 data11 data12
    2 name21 data21 data23
    3 name31 data31 data33
    etc.

    The columns are fixrd, but the rows vary.

    I frequently need to sort by column A or B, or AJ. I'd like to use a macro with a variable so I could tell it which column to sort on, either by entering the data (actual range or dynamic range name) or by attaching it to a control.

    Can anyone get me started in the right direction, remembering that I don't know VBA?

    Thanks,
    Cliff

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Open the Forms toolbar (View | Toolbars | Forms)

    Select a command button and put it on your worksheet.

    When the assign macro dialog comes up, choose New.

    Put this code between the Sub and End Sub statements
    (the sub should probably be Sub Button1_Click).


    dim strCol as String
    strCol = InputBox("Enter Column to Sort")

    strCol = strCol & "1"

    Range("A1:AJ100").Select
    Selection.Sort Key1:=Range(strCol), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom



    Now whenever you push the button it will bring up a dialog
    box asking for a column letter and do a sort on it.

    Notes:

    The Range statement should include you whole data range.
    The "1" in strCol = strCol & "1" should be changed to
    be the row number for the first row you want sorted.
    The Header:=xlYes assumes that the first row of your
    range should not be sorted (because it is a header). Change
    this to xlNO if the header is not included in your range.
    You will get erros if you do not enter valid row letter(s).

    Hope this helps.

    K

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    153
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-03 09:11, kkknie wrote:

    Open the Forms toolbar (View | Toolbars | Forms)

    Select a command button and put it on your worksheet.

    When the assign macro dialog comes up, choose New.

    Put this code between the Sub and End Sub statements
    [etc.]
    Works like a charm. This will save me a lot of work. Thank you very much for helping me.

    Cliff

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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