Looping thru rows and when a specific value is found in column 'N' sort all remaining rows in alpha order from column

[simmonsv]

I want to sort all remaining rows in alpha order on Vendor Name after P/U" value is found in column IncoT. There will ways be a value in column "Purch.Doc." I thought of coping entire sheet to another sheet delete rows where IncoT value is COL or P/U and then copy back but not sure that is the best solution. I have already sorted data by column IncoT. Any help on this would be greatly appreciated as always. Below is a sample of my sheet:

CONF	Purch.Doc.	Item	Buyer Name	Vendor	Vendor Name	Plant	Material	Material description	Due Date	Due Qty	Sched	Priority	IncoT
	4500520791	10	Kathy Moody	101135	PACTIV CORP	55	10058053	MC5-0601 WHT 6COMP FIBER MOLDED TRAY	09/12/13	5		P2	COL
	4500521046	10	Angela Starnes	200560	ARCOT MANUFACTURING CORP	55	923288	9636W WHT 1GAL SOFT-N-SMOOTH HAND SOAP	09/12/13	36	T	P3	P/U
	4500521046	20	Angela Starnes	200560	ARCOT MANUFACTURING CORP	55	923287	9560 NICE-N-CLEAN LAUNDRY DETERGENT	09/12/13	12	T	P3	P/U
	4500521046	30	Angela Starnes	200560	ARCOT MANUFACTURING CORP	55	10089247	9709 REFRESH 1GAL QUAT SANITIZER	09/12/13	4	T	P3	P/U
	4500521046	40	Angela Starnes	200560	ARCOT MANUFACTURING CORP	55	10089248	9584 FCC-84 1GAL DEGREASER / H/D CLNR	09/12/13	3	T	P3	P/U
	4500520958	10	Annette McCleskey	200144	G & A PALLET COMPANY	55	10098192	44"X90"4 WAY PALLET	09/11/13	60	T	P2	P/U
	4500521816	10	Angela Starnes	100418	GUARDIAN REPAIR & PARTS	55	10015147	5301ER MICRO FILTER F/WINDSOR SENSOR	09/16/13	10		P3	P/U
	4500520602	10	Kathy Moody	104690	HBM SUPPLY OF TEXAS INC	55	10089719	253 POLYKEN 2"X60YD SLV DUCT TAPE	09/12/13	9	T	P3	P/U
	4500520602	20	Kathy Moody	104690	HBM SUPPLY OF TEXAS INC	55	10089770	253 POLYKEN 1"X60YD SLV DUCT TAPE	09/12/13	3	T	P3	P/U
	4500520411	10	Kathy Moody	104934	MRI DPC INC	55	197960	H0369819 6"X90"X2"CORNER PROTECTOR	09/13/13	2,464.00		P2	P/U
	4500521083	10	Kathy Moody	101143	NATIONAL PAPER & PLASTICS CO	55	620708	D626790 VERSICOPY 8-1/2"X11"20# WHT COPY	09/16/13	240		P2	P/U
	4500520866	10	Angela Starnes	101293	POWR-FLITE	55	10066623	PS35E SPOTTER MACHINE W/AA163 TOOL	09/12/13	1	T	P1	P/U
	4500522011	10	Kathy Moody	100326	PRIMESOURCE INC	55	102830	GC410 10'X100' .004 CLR POLY FILM	09/16/13	10		P3	P/U
	4500519291	10	Annette McCleskey	102632	PRINT SOURCE INC	55	191108	1103 GIFT WRAP CLAIM CHECK	09/13/13	1,000.00		P1	P/U
	4500522017	10	Kathy Moody	100571	SCHERMERHORN BROS COMPANY	55	10069529	MAN1/4"1/4"X600' MANILLA ROPE	09/16/13	10		P2	P/U
	4500516850	10	Annette McCleskey	100502	STERLING MFG & DIST	55	10037276	5"X4-1/2"POLY FOAM CAP	09/04/13	1,000.00	T	P2	P/U
	4500521096	10	Annette McCleskey	100502	STERLING MFG & DIST	55	10102414	10-1/2X18X20 1.7WHT PE FOAM ENDCAP SET	09/11/13	5	T	P2	P/U
	4500520130	10	Annette McCleskey	100502	STERLING MFG & DIST	55	10070554	OPTIDISK FOAM G70 CHAR PU D/C ASSY W/TOP	09/13/13	1,000.00		P3	P/U
	4500521987	10	Kathy Moody	100379	TEXAS TECHNOLOGIES	55	10045628	DESICCANT 2-UNIT KRAFT	09/16/13	1		P2	P/U
	4500521239	10	Annette McCleskey	102751	THE LABEL SMITH	55	10080252	1-1/2"CLR CIRCLE LABEL	09/16/13	1,200.00		P2	P/U
	4500521577	10	Annette McCleskey	101506	UNITED STATIONERS SUPPLY CO	55	10084684	UNV07071 PERMANENT MARKER PEN STLYE	09/12/13	12	T	P3	P/U
	4500521577	20	Annette McCleskey	101506	UNITED STATIONERS SUPPLY CO	55	10084660	IVR52448 BLK RUBBER MOUSE PAD	09/12/13	2	T	P3	P/U
	4500521577	30	Annette McCleskey	101506	UNITED STATIONERS SUPPLY CO	55	10089636	UNV08850 POCKET HIGHLIGHTERS FLUOR COLOR	09/12/13	2	T	P3	P/U
	4500521577	40	Annette McCleskey	101506	UNITED STATIONERS SUPPLY CO	55	10084669	QUA37890 9X12 28# KRAFT CLASP ENVELOPE	09/12/13	5	T	P3	P/U
	4500521577	50	Annette McCleskey	101506	UNITED STATIONERS SUPPLY CO	55	10086166	GEM85T 2"STEEL T-PIN	09/12/13	5	T	P3	P/U
	4500521577	60	Annette McCleskey	101506	UNITED STATIONERS SUPPLY CO	55	10092246	EPS-ERC38BR RED/BLK RIBBON F/EPSON M188B	09/12/13	10	T	P3	P/U
	4500521654	10	Annette McCleskey	101506	UNITED STATIONERS SUPPLY CO	55	10056549	MONARCH 1-LINE 8-BAND LABEL GUN	09/11/13	42	T	P3	P/U
	4500521809	10	Annette McCleskey	101506	UNITED STATIONERS SUPPLY CO	55	10087929	MNK925403 INK ROLLER	09/12/13	126	T	P1	P/U
	4500521809	20	Annette McCleskey	101506	UNITED STATIONERS SUPPLY CO	55	10089636	UNV08850 POCKET HIGHLIGHTERS FLUOR COLOR	09/12/13	3	T	P1	P/U
	4500520596	10	Annette McCleskey	101506	UNITED STATIONERS SUPPLY CO	55	10084693	UNV43614 48X36 NAT CORK BULLETIN BOARD	09/05/13	8		P3	P/U
	4500520596	20	Annette McCleskey	101506	UNITED STATIONERS SUPPLY CO	55	10104283	BVCSB1420001233 48"X72"CORK BOARD	09/12/13	1		P3	P/U
	4500521000	10	Annette McCleskey	103365	WAL-MART	55	10093969	OUTLET PLUG COVER	09/17/13	40	T	P3	P/U
	4500521533	10	Annette McCleskey	104069	AVERY DENNISON	65	10087660	FG-122 (000239) 1-LINE WHT LABEL F/1131	09/13/13	320		P1	PPA
	4500521533	20	Annette McCleskey	104069	AVERY DENNISON	65	10069318	FG-151 .44"X.78"FLOUR GRN LABEL	09/13/13	192		P1	PPA
	4500521534	10	Annette McCleskey	104069	AVERY DENNISON	55	10087660	FG-122 (000239) 1-LINE WHT LABEL F/1131	09/16/13	384		P1	PPA
	4500521534	20	Annette McCleskey	104069	AVERY DENNISON	55	10069318	FG-151 .44"X.78"FLOUR GRN LABEL	09/16/13	256		P1	PPA
	4500521109	10	Annette McCleskey	104759	BASS SECURITY SERVICES INC	49	10092108	1-3/4"X3/8"PLAIN WHT DEACTIVATABLE LABEL	09/17/13	184		P3	PPA
	4500521110	10	Annette McCleskey	104759	BASS SECURITY SERVICES INC	55	10092108	1-3/4"X3/8"PLAIN WHT DEACTIVATABLE LABEL	09/17/13	230		P3	PPA
	4500521242	10	Annette McCleskey	104759	BASS SECURITY SERVICES INC	55	10092108	1-3/4"X3/8"PLAIN WHT DEACTIVATABLE LABEL	09/17/13	2,990.00		P2	PPA
	4500521244	10	Annette McCleskey	104759	BASS SECURITY SERVICES INC	65	10092108	1-3/4"X3/8"PLAIN WHT DEACTIVATABLE LABEL	09/17/13	1,610.00		P2	PPA
	4500521685	10	Angela Starnes	200717	BOBRICK WASHROOM EQUIPMENT INC	55	992809	B270 CONTURA S/S SANITARY NAPKIN RECEPT	09/17/13	12		P3	PPA
	4500520964	10	Kathy Moody	200341	CONDUCTIVE CONTAINERS INC	55	10076462	12.5X10.5X2.5 CONDUCTIVE BOX W/ A/S FOAM	09/13/13	27		P3	PPA
	4500520964	20	Kathy Moody	200341	CONDUCTIVE CONTAINERS INC	55	10032518	7X5X2-1/2 CONDUCTIVE BOX W/A/S FOAM	09/13/13	2		P3	PPA
	4500518750	10	Kathy Moody	101601	KERRY INGREDIENTS AND FLAVOURS	55	10083971	720697.LB6 TFF MULTI-COLORED SPRINKLES	09/13/13	70		P1	PPA
	4500518750	20	Kathy Moody	101601	KERRY INGREDIENTS AND FLAVOURS	55	10083970	Q11676.LB6 TFF CHOC SPRINKLES	09/13/13	10		P1	PPA
	4500516722	10	Kathy Moody	102814	LAGASSE BROTHERS INC	55	10059901	Q311 UPRIGHT SCRUBBER PAD HOLDER	09/04/13	3		P3	PPA
	4500519505	10	Kathy Moody	100701	PLAS-TIES	55	191886	P1140LT 11"NAT LADDER TIES	09/04/13	3		P3	PPA
	4500520882	10	Angela Starnes	100485	PRO TEAM INC	55	10001764	100331 COACH VAC VACUUM BAG 10QT	09/11/13	100		P3	PPA
	4500520882	20	Angela Starnes	100485	PRO TEAM INC	55	10015783	101678 50' YEL CORD W/CORD WRAP	09/11/13	11		P3	PPA
	4500519231	10	Kathy Moody	100335	REDDI-PAC INC	55	10101242	2X2X2X288 .180/.180 REDDI CRATE	09/16/13	224		P1	PPA
	4500519231	20	Kathy Moody	100335	REDDI-PAC INC	55	10101243	6X6X6X168 .200/.200 REDDI CRATE	09/16/13	120		P1	PPA
	4500519231	30	Kathy Moody	100335	REDDI-PAC INC	55	10101244	3X3X3X288 .160/.160 QUICK CRATE	09/16/13	196		P1	PPA
	4500519231	40	Kathy Moody	100335	REDDI-PAC INC	55	10101246	6X6X6X288 .200/.200 REDDI CRATE	09/16/13	180		P1	PPA
	4500519231	50	Kathy Moody	100335	REDDI-PAC INC	55	10101247	8X8X8X288 .200/.200 REDDI CRATE	09/16/13	120		P1	PPA
	4500519231	60	Kathy Moody	100335	REDDI-PAC INC	55	10103991	2X2X2X168 .180/.180 REDDI CRATE	09/16/13	112		P1	PPA
	4500519231	70	Kathy Moody	100335	REDDI-PAC INC	55	10103995	4X4X4X168 .160/.160 QUICK CRATE	09/16/13	196		P1	PPA
	4500519231	80	Kathy Moody	100335	REDDI-PAC INC	55	10103997	4X4X4X288 .160/.160 QUICK CRATE	09/16/13	196		P1	PPA
	4500519231	90	Kathy Moody	100335	REDDI-PAC INC	55	10103998	8"X8"X8"X168" .200/.200 REDDI CRATE	09/16/13	80		P1	PPA
	4500519231	100	Kathy Moody	100335	REDDI-PAC INC	55	10104001	3"X3"X3"X168" .160/.160 QUICK CRATE	09/16/13	196		P1	PPA
	4500521943	10	Kathy Moody	104901	STEWARTS PACKAGING	55	10035931	15500299 1-1/2"X2"LABEL P/SAFE HANDLING	09/13/13	12		P2	PPA
	4500521582	10	Kathy Moody	101118	TALO ENTERPRISES INC	55	10039439	SL339 3" PISTOL GRIP TAPE DISP	09/17/13	6		P3	PPA
	4500517963	10	Annette McCleskey	100134	TULSACK-A DIV OS DENMAR PROD INC	55	10032667	16"GOLD VOGUE BAG P/ACCESSORY PLACE	09/16/13	4		P2	PPA
	4500517963	20	Annette McCleskey	100134	TULSACK-A DIV OS DENMAR PROD INC	55	10032668	8"CUB KRAFT SHOP BAG P/ACCESSORY PLACE	09/16/13	4		P2	PPA
	4500517963	30	Annette McCleskey	100134	TULSACK-A DIV OS DENMAR PROD INC	55	10032669	16"GLD QUEEN BAG P/ACCESSORY PLACE	09/16/13	4		P2	PPA
	4500521044	10	Angela Starnes	101371	3M-HOME AND COMMERCIAL CARE	55	628610	3100 8753 20"AQUA BURNISH FLOOR PAD	09/16/13	7		P3	PPD
	4500521044	20	Angela Starnes	101371	3M-HOME AND COMMERCIAL CARE	55	920161	5L TNF QUAT DISF	09/16/13	12		P3	PPD
	4500521044	30	Angela Starnes	101371	3M-HOME AND COMMERCIAL CARE	55	920405	4100N NIAGARA 20"WHT POLISH PAD	09/16/13	30		P3	PPD
	4500521044	40	Angela Starnes	101371	3M-HOME AND COMMERCIAL CARE	55	920406	5100N NIAGARA 20"RED BUFFING PAD	09/16/13	7		P3	PPD
	4500521044	50	Angela Starnes	101371	3M-HOME AND COMMERCIAL CARE	55	920408	7200N NIAGARA 20"BLK STRIP PAD	09/16/13	17		P3	PPD
	4500521044	60	Angela Starnes	101371	3M-HOME AND COMMERCIAL CARE	55	962560	3000 SCOTCHBRITE POWER SPONGE SCRUBBER	09/16/13	2		P3	PPD
	4500521044	70	Angela Starnes	101371	3M-HOME AND COMMERCIAL CARE	55	10064256	3500N 20"NIAGARA TAN HOG HAIR PAD	09/16/13	34		P3	PPD
	4500521044	80	Angela Starnes	101371	3M-HOME AND COMMERCIAL CARE	55	962688	08004 8541 DOODLEBUG 4-5/8"BRN PAD	09/16/13	16		P3	PPD
	4500521044	90	Angela Starnes	101371	3M-HOME AND COMMERCIAL CARE	55	10032840	7400N NIAGARA HIGH PERF 20"BLK PAD	09/16/13	3		P3	PPD
	4500521044	100	Angela Starnes	101371	3M-HOME AND COMMERCIAL CARE	55	10083685	59152 EASY TRAP 8"X6"DUSTER	09/16/13	1		P3	PPD
	4500521044	110	Angela Starnes	101371	3M-HOME AND COMMERCIAL CARE	55	964338	5300 8413 20"BLU FLOOR CLNR PAD	09/16/13	2		P3	PPD
	4500521044	120	Angela Starnes	101371	3M-HOME AND COMMERCIAL CARE	55	964528	18066 3200 20"TOPLINE SPEED BURNISH PAD	09/16/13	5		P3	PPD
	4500521044	130	Angela Starnes	101371	3M-HOME AND COMMERCIAL CARE	55	969535	05606 3400 20"TAN BURNISH PAD	09/16/13	3		P3	PPD
	4500521044	140	Angela Starnes	101371	3M-HOME AND COMMERCIAL CARE	55	10062213	SPP20 20"SURFACE PREP PAD	09/16/13	2		P3	PPD
	4500521044	150	Angela Starnes	101371	3M-HOME AND COMMERCIAL CARE	55	10069223	SPP14X28 28"X14"MAROON SURFACE PREP PAD	09/16/13	3		P3	PPD
	4500521044	160	Angela Starnes	101371	3M-HOME AND COMMERCIAL CARE	55	10098758	RADIANCE 20"WHT POLISH PAD	09/16/13	1		P3	PPD
	4500509915	10	Annette McCleskey	104640	ANNJOY IMPORTS LLC	65	10102449	SM PINK&ORG PAPER BAG P/CC	09/13/13	662		P2	PPD
	4500509915	20	Annette McCleskey	104640	ANNJOY IMPORTS LLC	65	10102450	MED PINK&ORG PAPER BAG P/CC	09/13/13	340		P2	PPD
	4500509915	30	Annette McCleskey	104640	ANNJOY IMPORTS LLC	65	10102451	LG PINK&ORG PAPER BAG P/CC	09/13/13	367		P2	PPD
	4500520724	10	Annette McCleskey	200280	APOLLO DISTRIBUTORS	55	798705	#33 3-1/2X3-1/2X1 WHT JEWEL BOX W/COTTON	09/16/13	648		P2	PPD
	4500520724	20	Annette McCleskey	200280	APOLLO DISTRIBUTORS	55	709385	95/53 5-1/4X3-3/4X7/8 JEWELRY BOX	09/16/13	392		P2	PPD
	4500517701	10	Kathy Moody	200317	BROWN PAPER GOODS CO INC	55	306602	1306 FOIL BAG P/FRESH BAKED BREAD GARLIC	09/13/13	53		P1	PPD
	4500514419	10	Annette McCleskey	104659	CHARMING CHARLIE	55	10103245	PREPAID GOLD SHIMMER	08/02/13	432		P3	PPD
	4500514419	20	Annette McCleskey	104659	CHARMING CHARLIE	55	10103243	PREPAID PINK SHIMMER	08/02/13	432		P3	PPD
	4500514419	30	Annette McCleskey	104659	CHARMING CHARLIE	55	10103244	PREPAID SILVER SHIMMER	08/02/13	432		P3	PPD
	4500514420	10	Annette McCleskey	104659	CHARMING CHARLIE	55	10103245	PREPAID GOLD SHIMMER	08/02/13	432		P3	PPD
	4500514420	20	Annette McCleskey	104659	CHARMING CHARLIE	55	10103243	PREPAID PINK SHIMMER	08/02/13	432		P3	PPD
	4500514420	30	Annette McCleskey	104659	CHARMING CHARLIE	55	10103244	PREPAID SILVER SHIMMER	08/02/13	432		P3	PPD
	4500520741	10	Annette McCleskey	104659	CHARMING CHARLIE	55	10102447	PREPAID LUCKY/GLAMOUR SUBSCRIP BROCHURE	09/10/13	252		P3	PPD
	4500520214	10	Kathy Moody	104514	CSM BAKERY PRODUCTS NA	55	10081898	ICING F/BASKIN ROBBINS ICE CREME CAKE	09/17/13	70		P3	PPD
	4500521595	10	Kathy Moody	101382	ELKAY PLASTICS	55	10077408	RL 101475 10"X14".75MIL ROLL POLY BAG	09/12/13	596		P2	PPD
	4500521840	10	Kathy Moody	101382	ELKAY PLASTICS	55	121224	F20608 6"X8".002 ZIPLOCK BAG	09/13/13	1		P2	PPD
	4500521840	20	Kathy Moody	101382	ELKAY PLASTICS	55	122587	40F0912 9"X12".004 PLAIN POLY BAG	09/13/13	4		P2	PPD

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>

 

[venkat1926]

where incot colmn where is P/U?

anyhow use find function and get the row of P/U and selet rows next to that and till the end and sort

 

[simmonsv]

What I ended up doing is coping the work sheet to a new sheet doing a filter to delete the rows I didn't want, resort the remaining rows and copy pasting to the original sheet. It works but probably no the best use of code. Thank you for your feedback, I always appreciate this site and those that are so much more knowledgeable than I. I do have one other issued, I have code to find the last row on a sheet and now I want to go to the next empty row and copy from the previous row. Below is the code I have but am stumped on how to get to the next row below the last. I have two total rows, one of which I'll delete.' Use column A to determine where the last row of data occurs
myLastRow = Cells(Rows.Count, "A").End(xlUp).Row

For myRow = 2 To myLastRow
If Cells(myRow, "A") Like "Grand*" Then
' myRow +1.Select This is where I want to go to the next row down for myrow
' Copy value from column above
Cells(myRow, "H") = Cells(myRow - 1, "I")
Cells(myRow, "J") = Cells(myRow - 1, "K")
Cells(myRow, "L") = Cells(myRow - 1, "M")
Cells(myRow, "N") = Cells(myRow - 1, "O")
Cells(myRow, "P") = Cells(myRow - 1, "Q")
Cells(myRow, "R") = Cells(myRow - 1, "S")
Cells(myRow, "T") = Cells(myRow - 1, "U")
Cells(myRow, "V") = Cells(myRow - 1, "W")
Cells(myRow, "X") = Cells(myRow - 1, "Y")
Cells(myRow, "Z") = Cells(myRow - 1, "AA")
Cells(myRow, "AB") = Cells(myRow - 1, "AC")
Cells(myRow, "AD") = Cells(myRow - 1, "AE")
Else
If Cells(myRow, "A") = cell.Value & " Total" Then
Cells(myRow, "A").EntireRow.ClearContents
End If
End If

Next myRow Below is a sample of the end of my worksheet.

GORDMANS #044	3	10035672	P2516 BLANK YELLOW CLEARANCE	Light Yellow	BOX		0	$ -	3	$ 128.85	2	$ 85.90	2	$ 85.90	0	$ -	0	$ -	0	$ -	2	$ 85.90	0	$ -	0	$ -	2	$ 85.90	1	$ 42.95	$ 515.40
GORDMANS #044	Needed	10097581	XLPROIR INK ROLLER F/XL265P GUN	Light Yellow	EA		0	$ -	0	$ -	0	$ -	0	$ -	0	$ -	10	$ 45.00	0	$ -	0	$ -	0	$ -	0	$ -	0	$ -	0	$ -	$ 45.00
GORDMANS #044 Total							50	$ 1,290.71	110	$ 3,142.38	125	$ 3,268.06	70	$ 1,697.69	105	$ 2,145.01	71	$ 1,453.08	71	$ 1,244.19	82	$ 1,461.98	62	$ 1,564.30	51	$ 1,147.59	98	$ 1,558.74	99	$ 1,705.52	$ 21,679.26
Grand Total							50	$ 1,290.71	110	$ 3,142.38	125	$ 3,268.06	70	$ 1,697.69	105	$ 2,145.01	71	$ 1,453.08	71	$ 1,244.19	82	$ 1,461.98	62	$ 1,564.30	51	$ 1,147.59	98	$ 1,558.74	99	$ 1,705.52	$ 21,679.26

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="68"></colgroup>

 

[venkat1926]

It is always better to give a small extract of your data sheet and also sheet what result would be . then it is possible understand the requiorement and easier to test the code give btyou. so I have designed trivial data sheet given below .
Sheet1

*	A	B	C
1	hdng1	hdng2	hdng3
2	58	83	36
3	39	11	68
4	9	88	34
5	87	20	45
6	99	86	91
7	55	100	74
8	19	15	44
9	3	70	64
10	3	64	10

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

now it is not clear what you want. do yu want A2 to c10 cut copied to A11 onwards.

FIRST COPY SHEETS 1 TO SHEET 2 - IMPORTANT

now try this macro

Sub test()
    Dim r As Range, lastcell As Range
    Worksheets("sheet1").Cells.Clear
    Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("a1")
    Worksheets("sheet1").Activate
    Set r = Range(Range("A2"), Range("A2").End(xlToRight).End(xlDown))
    MsgBox r.Address
    Set lastcell = Range("A2").End(xlDown).Offset(1, 0)
    r.Cut
    lastcell.Select
    ActiveSheet.Paste




End Sub

if this is not what you want then take my data and give expected result