Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Sort a worksheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2016
    Location
    Northwest Arkansas
    Posts
    90
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Sort a worksheet

    I have a worksheet with columns A through R. I need to sort this worksheet by the value that is in Column C. I have searched for the answer and have tried several suggestions, but the worksheet never sorts. In my code below I have commented out all the things I have tried. Is what I am trying to do possible, and if so, how?

    Code:
    Private Sub cmdBuildOrders_Click()
    cmdBuildOrders.Caption = "Running"
    cmdBuildOrders.BackColor = RGB(255, 255, 0)
    cmdBuildOrders.ForeColor = RGB(0, 0, 0)
    Application.Wait (Now + TimeValue("0:00:01")) 'allows command button to change text and back color
    
    lastrow = Sheet3.Range("A100").End(xlUp).Row
    
    Sheet3.Activate
    'Range("C1:C17", Range("C1").End(xlDown)).Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes
    
    'ActiveSheet.Sort.SortFields.Clear
    'Columns("A:R").Sort Key1:=Range("C2:C17"), Order1:=xlAscending, Header:=xlYes
    
    'Range("A2:R17").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes
    
    'Range("C2").CurrentRegion.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes
    
    'LastRowSort = Sheet3.Cells(Rows.Count, 2).End(xlUp).Row
    'Range("A2:R" & LastRowSort).Sort key1:=Range("C2:C" & LastRowSort), _
       order1:=xlAscending, Header:=xlNo
    
    
    'For Row = 2 To LastRow
    '    Sheet4.Cells(Row, 2).Value = "MPS"                             'Customer Code Parent Row
    '    Sheet4.Cells(Row, 3).Value = "ATNA"                           'Project Code Parent Row
    '    Sheet4.Cells(Row, 5).Value = "Standard"                     'Type Parent Row
    '    Sheet4.Cells(Row, 6).Value = "Pick Pack and Ship"    'Attribute Parent Row
    '    Sheet4.Cells(Row, 13).Value = "False"                         'IsAccessory Child Row
    '    Sheet4.Cells(Row, 14).Value = "False"                         'IsConfiguration Child Row
    '    Sheet4.Cells(Row, 15).Value = "False"                         'Reservation Parent Row
    '
    'Next Row
    cmdBuildOrders.Caption = "Build Customer Page"
    cmdBuildOrders.BackColor = RGB(0, 176, 80)
    cmdBuildOrders.ForeColor = RGB(255, 255, 255)
    End Sub
    Computerman

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,880
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Sort a worksheet

    Dumb question here....but are you sure Sheet3 is the correct sheet ?
    Have you tried activating the sheet by name to be sure?
    Can you Upload your file to a sharing site, Dropbox for instance, and then post the link back here ?
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,101
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Sort a worksheet

    Code:
    Range("A1:R17").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes
    
    Range("C2").CurrentRegion.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes
    These above syntax should work.

    What's the nature of the data in column C ?
    Can you manually sort using the menu?

    It's possible to have formulas that use relative row references that produce the same result order when you sort the formulas. In other words, when you sort the formulas, the formulas recalculate and return the same pre-sorted order.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  4. #4
    Board Regular
    Join Date
    Mar 2016
    Location
    Northwest Arkansas
    Posts
    90
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort a worksheet

    Michael,
    I am absolutely positive that sheet 3 is the correct sheet. I even double checked right now to be sure. I am not comfortable posting my file to any file sharing site as it contains customer sensitive information.

  5. #5
    Board Regular
    Join Date
    Mar 2016
    Location
    Northwest Arkansas
    Posts
    90
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort a worksheet

    Quote Originally Posted by AlphaFrog View Post
    Code:
    Range("A1:R17").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes
    
    Range("C2").CurrentRegion.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes
    These above syntax should work.

    What's the nature of the data in column C ?
    Can you manually sort using the menu?

    It's possible to have formulas that use relative row references that produce the same result order when you sort the formulas. In other words, when you sort the formulas, the formulas recalculate and return the same pre-sorted order.
    AlphaFrog,
    I noticed that the range was changed in your line 1 above. I changed my code to match your line. It did not work. The data that is in column C is address information, street number and street name. I can manually sort the data. There are no formula in the spreadsheet, it is a direct data pull from a sharepoint page.
    Computerman

  6. #6
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,880
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Sort a worksheet

    @Computerman...OK, understand that.
    Hopefully @AlphaFrog has provided some help.

    Maybe the street number is throwing your sort out.
    Last edited by Michael M; Sep 8th, 2019 at 09:45 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  7. #7
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,101
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Sort a worksheet

    IDK

    Can you post a desensitized workbook that exhibits the same issue?
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Sort a worksheet

    Do you have any rows or columns that are completely blank?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Mar 2016
    Location
    Northwest Arkansas
    Posts
    90
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort a worksheet

    Fluff,
    while there are no Rows that are completely bank, There is one column that is completely empty. I will test taking that column out and see what happens

    Computerman

  10. #10
    Board Regular
    Join Date
    Mar 2016
    Location
    Northwest Arkansas
    Posts
    90
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort a worksheet

    ***SOLVED***
    Fluff, and all who made suggestions,
    Thank you all for your suggestions. Following Fluff’s suggestion I removed a blank column from the spreadsheet and using AlphaFrog’s suggestion of Range(A1:Q17)… my spreadsheet is now sorting correctly.
    Again, thank you all for your assistance it is appreciated
    Computerman

Some videos you may like

User Tag List

Tags for this Thread

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
  •