VBA sorting Excel rows in ascending order but excluding first row?

23,846

Looks like the sorting glitches up a little when there isn't anything to sort, since obviously when there's only one cell, it doesn't have anything to sort and might as well just be skipped.

So I'd just recommend checking that there's more than one row filled in first, and skip the sorting if there isn't.

' Sort by REPTNO field
Dim xlSort As XlSortOrder
xlSort = xlAscending

With Worksheets("Export")
    If .Range("B3") <> ""
        .range("B2:B" & rowCnt).Sort Key1:=.range("B2"), Order1:=xlSort, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    End if
End With
Share:
23,846
Analytic Lunatic
Author by

Analytic Lunatic

Software Developer by Day, Web Designer by Night.

Updated on July 03, 2020

Comments

  • Analytic Lunatic
    Analytic Lunatic over 3 years

    I was using the below code successfully to, once I populate a worksheet with data, sort the data in ascending order based on the B column, the first row being record headers; B column being the REPTNO column.

    For sample data I had B1 containing REPTNO and B2-B17 containing SR0238-SR0253. Everything was good, but then when I cleared out B2-B17 and submitted to user for testing, something strange happened.

    My user entered a record into row 2 with the value of JF0007 for REPTNO. When my below code processes, my first and second row are flipping (J coming before R), causing my "Header" column to be on the second row instead of remaining on the first.

    What do I need to change to sort only the second row onward, excluding the first row? My row 1 needs to remain as row 1 for when I output to a .txt file.

    The value of rowCnt in this situation is 2 (The header row + the 1 data row that is entered.

    EDIT2:

        ' Sort by REPTNO field
        Dim xlSort As XlSortOrder
        xlSort = xlAscending
    
        If rowCnt > 2 Then
            With Worksheets("Export")
    
                .range("B2:B" & rowCnt).Sort Key1:=.range("B2"), Order1:=xlSort, Header:=xlNo, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal
            End With
        End If
    

    It appears to only be swapping the first row (Header row) when there is 1 record entered (row 2). When I inputted a second data record into row 3 REPTNO = JF0005, the REPTNO value remained as B1. It does however appear this code is not functioning as I thought it was.

    I need to sort all the rows (row 2 - however many rows there are) based on their REPTNO value. Sadly, my current code is ONLY ordering the data in the REPTNO column, or B.

  • Analytic Lunatic
    Analytic Lunatic over 10 years
    I did something similar, checking the total number of rows for being greater than 2. However I did note that my code is only sorting the REPTNO or B column, not sorting the entire row. For instance when I had JF0007 followed by JF0005, when sorted, JF0005 appeared before JF0007, but the two only switched places in their column. Where JF0005 ended up was with data that was entered with JF0007. I'm needing all data associated with the sorted column value to move with the value into it's new sort order.
  • Analytic Lunatic
    Analytic Lunatic over 10 years
    Thanks for the help mattboy! Tim got me straightened out. Need to expand the range of my .Sort