VBA sorting Excel rows in ascending order but excluding first row?
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
Analytic Lunatic almost 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
Bcolumn, the first row being record headers;
Bcolumn being the
For sample data I had B1 containing
REPTNOand 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
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
rowCntin this situation is 2 (The header row + the 1 data row that is entered.
' 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
REPTNOvalue 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
REPTNOvalue. Sadly, my current code is ONLY ordering the data in the
Analytic Lunatic almost 10 yearsI 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
Bcolumn, not sorting the entire row. For instance when I had
JF0005, when sorted,
JF0007, but the two only switched places in their column. Where
JF0005ended 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 almost 10 yearsThanks for the help mattboy! Tim got me straightened out. Need to expand the range of my .Sort