Articles

Solved Macro to find a range and then sort it

January 16, 2013 at 06:16:47
Specs: Windows XP

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


See More: Macro to find a range and then sort it

Report •


#1
January 16, 2013 at 08:51:58
✔ Best Answer

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.


Report •

#2
January 16, 2013 at 09:49:40

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


Report •

Related Solutions


Ask Question