Solved Macro to find a range and then sort it

I assume that the range you need to sort is all in Column B. The following code will do what you ask, although I'm not sure that what you asked for is really what you want. Allow me to explain... When you say "sort them smallest item first" I assume you mean Ascending order. For numerical values such as 2, 212, 22, 4 and 3, Excel will sort them like this: 2 3 4 22 212 However, for Text values like Z2, Z212, Z22, Z4 and Z3, "smallest" takes on a different meaning to Excel. Excel is going to sort that list like this: Z2 Z212 Z22 Z3 Z4 If that is not what you are after, please provide some examples of your data so we can see what we are working with. Sub SortByZ() 'Determine last row with data in Column B lastRw = Sheets(1).Range("B" & Rows.Count).End(xlUp).Row 'Determine how many cells have Z as the first character numZ = WorksheetFunction.CountIf(Sheets(1).Range("B11:B" & lastRw), "Z*") 'Sort B11 through last row with Z as the first character Sheets(1).Sort.SortFields.Clear Sheets(1).Sort.SortFields.Add Key:=Range("B11:B" & 11 + numZ - 1) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With Sheets(1).Sort .SetRange Range("B11:B" & 11 + numZ - 1) .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you DerbyDad03, you were correct to note that I probably was not describing the sort correctly but I have edited your code to include columns B:F and the result appears to give me what I was looking for, the edited code is: Sub SortByZ() 'Determine last row with data in Column B lastRw = Sheets("BOM").Range("B" & Rows.Count).End(xlUp).Row 'Determine how many cells have Z as the first character numZ = WorksheetFunction.CountIf(Sheets("BOM").Range("B11:B" & lastRw), "Z*") 'Sort B11 through last row with Z as the first character ActiveWorkbook.Worksheets("BOM").Sort.SortFields.Clear ActiveWorkbook.Worksheets("BOM").Sort.SortFields.Add Key:=Range("B11:B" & 11 + numZ - 1) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("BOM").Sort .SetRange Range("B11:F" & 11 + numZ - 1) .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub The original data looked like Item Stk code Qty Circuit Reference Value Description / Note ZISSUE 1 ZMOD 1 ZREV 1 Revision ZAS 1 Assembly ZTS Test 1 ZSW00038 2 SW2 SW3 FSMRA4J PBSW-TH-RA 2 ZSW00008 1 S1 3315C-001-016 3 ZSW00007 1 SW1 7906H-001-000 7906 4 ZRS00312 1 R425 1K 402 5 ZRS00309 1 RN6 100K CHIP8 6 ZRS00308 1 R93 240K 603 7 ZRS00307 1 R370 75K 603 . . . 179 NoFit - DELETE FROM PARTS LIST 8 and now after the sort looks like: ZISSUE 1 Issue ZMOD 1 Mod level ZREV 1 Revision ZAS 1 Assembly ZTS Test 1 ZCN00050 6 J1 J9 PL2 PL3 PL4 PL5 M20-9990305 2 ZCN00054 6 LNK1 LNK2 LNK3 LNK4 LNK5 LNK6 Top 3 ZCN00059 1 PL8 M20-9990505 HEADER5 4 ZCN00062 2 TP18 TP24 20-2137D TP 5 ZCN00070 6 J3 J4 J5 J6 J7 J8 M20-9980505 6 ZCN00092 1 PL7 M20-9990405 HEADER4 . . . Some of the alignment has not copied correctly but I'm sure you get the idea. It's been a while since I been on this site but it is good to see that you continue to provide excellent solutions. Thanks again cach