|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:
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:
If that is not what you are after, please provide some examples of your data so we
can see what we are working with.
'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.Add Key:=Range("B11:B" & 11 + numZ - 1) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("B11:B" & 11 + numZ - 1)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.