In excel using a macro I want to to select a range of cells starting at B11 and going down until the data in column B does not start with the letter āZā, then sort them smallest item first. Any suggestions would be appreciated

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

212However, 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

Z4If 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 likeItem 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 8and 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

Ask Your Question

Weekly Poll

Do you think the cable television business is going to change drastically in the next 5 years?

Discuss in The Lounge

Poll History