Macro to select certain rows

THRASHER69 · Jul 3, 2012

I am needing help with a macro to select the header and rows below till the next header is reached if column L is less than column J for the whole sheet which can be up to 5000 lines or more. Then copy all of those to a new sheet. Any help would be greatly appreciated. Here is part of the spreadsheet for a visual:

Product number	Name	Name	Finish date	Planned Qty	Mfg U/M	Lvl	Component	Name	Required Qty	Bas U/M	Available	Drawing Number
26010000	HOPPER CAR VALVE LESS COVER	HOPPER CAR VALVE LESS COVER	70512	20	EA
		HOPPER CAR VALVE LESS COVER				1	21009428	E-RING FOR T164 SHAFT & HCV	180	EA	1324	PF
		HOPPER CAR VALVE LESS COVER				1	26901040	E-RING F/HCV LOCK PIN ASSY-SS	20	EA	99
		HOPPER CAR VALVE LESS COVER				1	26901005	SP'L 5/8"SAFETY HINGE BOLT	20	EA	144
		HOPPER CAR VALVE LESS COVER				1	26901010	HANDLE ONLY - HCV	20	EA	94	8116
		HOPPER CAR VALVE LESS COVER				1	26901020	LOCKING PIN FOR HCV	20	EA	57	26901020
		HOPPER CAR VALVE LESS COVER				1	26901021	GREASE FITTING STRAP CAP - HCV	40	EA	851
		HOPPER CAR VALVE LESS COVER				1	26901028	5/8"LOCK NUT FOR HCV	60	EA	394	PF
		HOPPER CAR VALVE LESS COVER				1	26901030	1/2"*5"LOCKING EYEBOLT FOR HCV	60	EA	293	26901030
		HOPPER CAR VALVE LESS COVER				1	26901031	WASHER FOR 1/2"*5"HCV EYEBOLT	60	EA	76	PF
		HOPPER CAR VALVE LESS COVER				1	26901033	1/2"20NF*2.5"HX HD BOLT-PLATED	60	EA	300
		HOPPER CAR VALVE LESS COVER				1	26901036	HOPPER CAR VALVE BODY	20	EA	7	26901036
		HOPPER CAR VALVE LESS COVER				1	26901037	HOPPER CAR VALVE SHAFT	20	EA	74	26901037
		HOPPER CAR VALVE LESS COVER				1	26901050	SPRING FOR HCV PIN	20	EA	157
		HOPPER CAR VALVE LESS COVER				1	26901900	HOPPER CAR VALVE FLAPPER PLATE	20	EA	48	26901900
		HOPPER CAR VALVE LESS COVER				1	26909020	LOCKING MECHANISM FOR HCV	60	EA	269	26909020
		HOPPER CAR VALVE LESS COVER				1	26909070	3/4"SAFETY EYEBOLT ASSY - HCV	20	EA	0	26909070
		HOPPER CAR VALVE LESS COVER				1	99800020	1/8"GREASE ZERK (THD TYPE)	40	EA	589
		HOPPER CAR VALVE LESS COVER				1	99903260	#326 BN O'RING - 1.25"UNION	40	EA	194
		HOPPER CAR VALVE LESS COVER				1	99996010	1/2"LOCK WASHER	40	EA	3703
		HOPPER CAR VALVE LESS COVER				1	99996204	9/16"WASHER (13/16"OD)	20	EA	102
		HOPPER CAR VALVE LESS COVER				1	99997400	3/16"*1"DRIV-LOK PIN.TYPE E	20	EA	5127
		HOPPER CAR VALVE LESS COVER				1	99998300	1/2"*1"LG BOLT	40	EA	416
		HOPPER CAR VALVE LESS COVER				1	99999020	1/2"LOCK NUT W/NYLON INSERT	120	EA	1210
Product number	Name	Name	Finish date	Planned Qty	Mfg U/M	Lvl	Component	Name	Required Qty	Bas U/M	Available	Drawing Number
26909070	3/4"SAFETY EYEBOLT ASSY - HCV	3/4"SAFETY EYEBOLT ASSY - HCV	70312	20	EA							26909070
		3/4"SAFETY EYEBOLT ASSY - HCV				1	26901001	MOD 3/4"X6.75" EYEBOLT- HCV	20	EA	62	26909070
		3/4"SAFETY EYEBOLT ASSY - HCV				1	10909101	3/4"-10NC HEAVY HEX NUT-HHS	20	EA	1164	PF
		3/4"SAFETY EYEBOLT ASSY - HCV				1	99997201	SPRING PIN 3/16"*1.25"-BRONCO	20	EA	407
		3/4"SAFETY EYEBOLT ASSY - HCV				1	99996220	3/4"WASHER (2"OD*1/8"THK)	20	EA	236
Product number	Name	Name	Finish date	Planned Qty	Mfg U/M	Lvl	Component	Name	Required Qty	Bas U/M	Available	Drawing Number
29510021	1.25"PCSB UPPER GLAND ASSY (HPLUG) W/NBR V-PKG)	1.25"PCSB UPPER GLAND ASSY (HPLUG) W/NBR V-PKG)	70212	6	EA
		1.25"PCSB UPPER GLAND ASSY (HPLUG) W/NBR V-PKG)				1	11909305	3/4"PIPE PLUG F/ORG	6	EA	1067
		1.25"PCSB UPPER GLAND ASSY (HPLUG) W/NBR V-PKG)				1	29600527	1.25"NITRILE V-RING PKG-PCSB	6	SET	438
		1.25"PCSB UPPER GLAND ASSY (HPLUG) W/NBR V-PKG)				1	29903070	CAP F/PCSB	6	EA	189	29903070
		1.25"PCSB UPPER GLAND ASSY (HPLUG) W/NBR V-PKG)				1	29903090	1"-1.31" HPLUG BODY	6	EA	13	29903090
		1.25"PCSB UPPER GLAND ASSY (HPLUG) W/NBR V-PKG)				1	29908360	1.25"PCSB SPLIT BUSHING	12	EA	474	29908360
		1.25"PCSB UPPER GLAND ASSY (HPLUG) W/NBR V-PKG)				1	99800021	1/4" NPT GREASE ZERK	6	EA	1326
Product number	Name	Name	Finish date	Planned Qty	Mfg U/M	Lvl	Component	Name	Required Qty	Bas U/M	Available	Drawing Number
29510061	1.5"PCSB UPPER GLAND ASSY (HPLUG) W/NBR V-PKG	1.5"PCSB UPPER GLAND ASSY (HPLUG) W/NBR V-PKG	70212	25	EA
		1.5"PCSB UPPER GLAND ASSY (HPLUG) W/NBR V-PKG				1	11909305	3/4"PIPE PLUG F/ORG	25	EA	1067
		1.5"PCSB UPPER GLAND ASSY (HPLUG) W/NBR V-PKG				1	29600567	1.5"NITRILE V-RING PKG-PCSB (	25	SET	221
		1.5"PCSB UPPER GLAND ASSY (HPLUG) W/NBR V-PKG				1	29903070	CAP F/PCSB	25	EA	189	29903070
		1.5"PCSB UPPER GLAND ASSY (HPLUG) W/NBR V-PKG				1	29903100	1.5" HPLUG BODY	25	EA	42	29903100
		1.5"PCSB UPPER GLAND ASSY (HPLUG) W/NBR V-PKG				1	29908370	1.5"PCSB SPLIT BUSHING	50	EA	1329	29908370
		1.5"PCSB UPPER GLAND ASSY (HPLUG) W/NBR V-PKG				1	99800021	1/4" NPT GREASE ZERK	25	EA	1326
Product number	Name	Name	Finish date	Planned Qty	Mfg U/M	Lvl	Component	Name	Required Qty	Bas U/M	Available	Drawing Number
29539997ATX	STUFF BOX LEAK DETECTOR ONLY-1500 PSI (W/18 PSI EXP SWITCH)	STUFF BOX LEAK DETECTOR ONLY-1500 PSI (W/18 PSI EXP SWITCH)	70212	51	EA
		STUFF BOX LEAK DETECTOR ONLY-1500 PSI (W/18 PSI EXP SWITCH)				1	29905000	6 WAY CROSS F/HPLUG LEAK DET	51	EA	0	29905000
		STUFF BOX LEAK DETECTOR ONLY-1500 PSI (W/18 PSI EXP SWITCH)				1	29512ATX	1/2" PRESSURE SWITCH F/SB LEAK	51	EA	74	PF
		STUFF BOX LEAK DETECTOR ONLY-1500 PSI (W/18 PSI EXP SWITCH)				1	37300104W	3/4"2000# SS BV-2PC/SEAL WELDE	51	EA	375
		STUFF BOX LEAK DETECTOR ONLY-1500 PSI (W/18 PSI EXP SWITCH)				1	32960424	3/4"2500CWP FIG300 HEX NUT	51	EA	53	32960424
		STUFF BOX LEAK DETECTOR ONLY-1500 PSI (W/18 PSI EXP SWITCH)				1	32960640	3/4"4M 300 TL SUB	51	EA	88	32960640
		STUFF BOX LEAK DETECTOR ONLY-1500 PSI (W/18 PSI EXP SWITCH)				1	32960657	3/4"4M 300 THD SUB	51	EA	88	32960657
		STUFF BOX LEAK DETECTOR ONLY-1500 PSI (W/18 PSI EXP SWITCH)				1	99902151	#215 VITON O'RING - 3/4"UNION	51	EA	162
		STUFF BOX LEAK DETECTOR ONLY-1500 PSI (W/18 PSI EXP SWITCH)				1	1.29E+23	3/4" NPT X 2" SCH. 80 NIP	102	EA	241	PF
		STUFF BOX LEAK DETECTOR ONLY-1500 PSI (W/18 PSI EXP SWITCH)				1	11909302	1/2"NPT PIPE PLUG F/ORG	102	EA	688
		STUFF BOX LEAK DETECTOR ONLY-1500 PSI (W/18 PSI EXP SWITCH)				1	11909305	3/4"PIPE PLUG F/ORG	153	EA	1067
		STUFF BOX LEAK DETECTOR ONLY-1500 PSI (W/18 PSI EXP SWITCH)				1	29511ATX-TAG	LABEL F/EXP-PROOF PRESSURE SWI	51	EA	111	29511000-TAG
		STUFF BOX LEAK DETECTOR ONLY-1500 PSI (W/18 PSI EXP SWITCH)				1	BX-LD	BOX: LD BOX W/ INSERTS	51	EA	45
		STUFF BOX LEAK DETECTOR ONLY-1500 PSI (W/18 PSI EXP SWITCH)				1	129SE001	1/2"X3/4" SCH. 80 SWAGE NIPPLE	51	EA	93
Product number	Name	Name	Finish date	Planned Qty	Mfg U/M	Lvl	Component	Name	Required Qty	Bas U/M	Available	Drawing Number
2980316B	2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND	2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND	70212	8	EA
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11600169H	1.5"COMP"G"TOP CONE (PC) R3	24	EA	1447	PF
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11620161	1.5"HSN(HNBR) DOME PKG (PC)	24	EA	3139
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11901070	BODY F/DPSB W/COMP BOLT.NUT&RI	8	EA	1038
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11901160	LOWER GLAND F/SB'S (PATT# 296)	8	EA	1582	11991160
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11908121	1.37-1.5"COMP RING F/DP-PATT#4	8	EA	1770	6551
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11908156	1.5"LARGE BRONZE SPLIT RING-DO	8	EA	1124	11908156
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11908158	ORG ADAPTER RING F/DOME PKG	8	EA	67	11908158
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11908159	#330 SQ BN O'RING-ORG W/DOME P	8	EA	528	11908159
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	29906080	1.5"HTD LWR SPLIT BUSH W/RET	8	EA	36	29906080
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11909011	5/8"*4.5"BOLT F/IVSB.IVDP.SB-D	16	EA	13992
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11909013	5/8"*6"BOLT F/SBT	16	EA	1240
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11909103	5/8"HEAVY HX NUT F/DPSB.SB.WFM	32	EA	18251
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11909104	5/8"HVY SQUARE NUT F/ALL SB'S	32	EA	12851
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11909201	5/8"LOCK WASHER F/DPSB.WFM	96	EA	32728
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11909202	5/8"FLAT WASHER F/ALL SB'S	32	EA	30109
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11909302	1/2"NPT PIPE PLUG F/ORG	8	EA	688
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11902031	1.37-1.5"LUB CAP (PATT #105A)	8	EA	1800	11902031
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11901021	1.37-1.5"LUB BODY W/ZERK	8	EA	128	11901021
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11909001	3/8"-16*2"CAP SCREW F/LUB	16	EA	7000
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11909100	3/8"SQUARE NUT F/LUB & ORG	16	EA	8538
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	11909200	3/8"INT-EXT LOCK WASHER F/LUB.	16	EA	6754
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	29903080	PCSB ADAPTER SPOOL	8	EA	8	29903080
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	29903151	2.5"8RD PCSB BASE	8	EA	20	29903151
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	29908340	LH SPRING - PCSB	8	EA	677	29908340
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	29908350	RH SPRING - PCSB	8	EA	521	29908350
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	29909208	5/8"*1.75"BOLT F/PCSB	48	EA	3403
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	29909207	5/8"*2"BOLT F/PCSB	48	EA	3293
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	99901461	#146 VITON O'RING-PCSB ADAPTER	8	EA	365
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	99902521	#252 VITON O'RING-PCSB BASE	8	EA	879
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	99904301	#430 VITON O'RING - DPSB BASE	8	EA	1944
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	29908351	PCSB FLAPPER	8	EA	1417	29908351
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	29908352	PCSB FLAPPER WEAR PAD	8	EA	1169	29908352
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	29908353	#10-24*.75 SOC HD CAP SCREW	16	EA	1769
		2.5"8RD PCSB W/1.5" DOME/"G" BASE CONES & LUB UPPER GLAND				1	29908354	#10-24 NYLON LOCK HEX NUT	16	EA	1771

Sorry for the size. I wasn't sure how to make it smaller

Thank you

Warship · Jul 3, 2012

Based on the data sample you posted, what should your end result look like.

If I understand, it would be two sheets, each with two components and headers?

Any particular sheet naming convention?

THRASHER69 · Jul 3, 2012

Two sheets is basically what I'm after. I will try to explain a bit better using the above example. I'm looking for a macro that will check column with the header "Available" (Column L) for the whole sheet. It will check each number under "Available" (Column L) and see if it is less than the numbers under the column "Required Qty"(Column J). Under the 1st and 5th headers in the above example, both have quantities in column L that are less than the quantities in column J. The 2nd, 3rd, 4th, and 6th headers do not have quantities under column L that are less than the quantities in column J. Here is where it can go one of two ways.
1. I need the macro to check the whole sheet and cut the entire header and all the rows underneath until it reaches the next header for the ones that column "L" is less than column "J" and paste them to a new sheet named "Low Inventory" which in the above example would be the 1st and 5th headers and the rows underneath them, leaving the 2nd, 3rd, 4th, and 6th headers and data on the original sheet
2. I need the macro to check the whole sheet and delete the header and all the rows underneath the header until it reaches the next header for the ones that column "L" is equal to or greater than column "J". In the above example the macro would delete the 2nd, 3rd, 4th, and 6th headers and the data underneath them leaving the 1st and 5th headers and the data underneath on the original sheet.
I want to basically separate the ones that do not have enough inventory from the ones that do keeping the headers attached for each one. Either way I listed will work.

Hope this explains what I'm asking for a little better.

Thank you

THRASHER

THRASHER69 · Jul 3, 2012

I forgot to mention, I'm using Excel 2003

THRASHER69 · Jul 4, 2012

Did that answer your question Warship?

THRASHER69 · Jul 5, 2012

Warship? Anybody...?

Warship · Jul 7, 2012

Warship said:
Based on the data sample you posted, what should your end result look like.

If I understand, it would be two sheets, each with two components and headers?

Any particular sheet naming convention?

I meant I wanted you to manually create the sheets and post them like you did your sample data.
I think I get it now but... "a picture = 1000 words"

Warship · Jul 7, 2012

see if this is what you're looking for...

Code:

Sub Inventory()
    Dim found As Range
    Dim rgSource As Range
    Dim shSource As Worksheet
    Dim shDestin As Worksheet
    Dim shLo As Worksheet
    Dim shOk As Worksheet
    Dim bLow As Boolean
    Dim nextRow As Long
    Dim sShSource As String
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Low Inventory").Delete
    Sheets("Okay Inventory").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Low Inventory"
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Okay Inventory"
    
    Set shSource = Sheets("Sheet1") 'change to suit
    Set shLo = Sheets("Low Inventory")
    Set shOk = Sheets("Okay Inventory")
    
    shSource.Activate
    shSource.Rows(1).Copy
    shLo.Cells(1, 1).PasteSpecial Paste:=xlPasteColumnWidths
    shOk.Cells(1, 1).PasteSpecial Paste:=xlPasteColumnWidths
    Application.CutCopyMode = False
    
    Set found = shSource.Range("L:L").Find("Available", Range("L1"), , xlWhole)
    Do While Not found Is Nothing
        If found.Row = 1 Then
            Set rgSource = shSource.Cells(1, 1).CurrentRegion
        Else
            Set rgSource = shSource.Rows("1:" & found.Row - 1)
        End If
        bLow = False
        For i = 2 To rgSource.Rows.Count
            If rgSource.Cells(i, "L") < rgSource.Cells(i, "J") Then
                bLow = True
                Exit For
            End If
        Next i
        If bLow Then
            Set shDestin = shLo
        Else
            Set shDestin = shOk
        End If
        nextRow = shDestin.Cells(1, 1).CurrentRegion.Rows.Count
        If nextRow > 1 Then nextRow = nextRow + 1
        rgSource.Copy shDestin.Cells(nextRow, 1)
        rgSource.EntireRow.Delete
        Set found = shSource.Range("L:L").Find("Available", Range("L1"), , xlWhole)
    Loop
    
    sShSource = shSource.Name
    Application.DisplayAlerts = False
    shSource.Delete
    Application.DisplayAlerts = True
    shOk.Name = sShSource
End Sub

THRASHER69 · Jul 8, 2012

Wardhip,

I will give it a go when I get back to work Monday. If that's not what I was looking for I will create and post the before and after sheets for you. I will let you know either way.

I really appreciate the help!

THRASHER

THRASHER69 · Jul 9, 2012

Warship...sorry, I fat fingered the keys

Macro to select certain rows

THRASHER69

Board Regular

Excel Facts

Warship

Well-known Member

THRASHER69

Board Regular

THRASHER69

Board Regular

THRASHER69

Board Regular

THRASHER69

Board Regular

Warship

Well-known Member

Warship

Well-known Member

THRASHER69

Board Regular

THRASHER69

Board Regular

Similar threads

Forum statistics

Share this page

Macro to select certain rows

Board Regular

Excel Facts

Well-known Member

Board Regular

Board Regular

Board Regular

Board Regular

Well-known Member

Well-known Member

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock