Delete Rows Matching Data Criteria

[SOLTEC]

Excel 2010
	D	E	F	G	H
1	Supplier ID	Reference No.	Wholesale		Quantity
2	10-01	1001			Replacement Boot Laces
3	10-012	10012			Replacement Boot Laces
4	10-50 OD	1050			Blousing Garters
5	10-502	10502			Blousing Garters
6	10-505	10505			Blousing Garters
7	10-507	10507			Blousing Garters
8	10-89 04 W	1089			GI Extreme Cold Weather Bunny Boots
9	10-89 05 W	1089			GI Extreme Cold Weather Bunny Boots
10	10-89 08 R	1089			GI Extreme Cold Weather Bunny Boots
11	10-89 11 W	1089			GI Extreme Cold Weather Bunny Boots
12	10-89 14 R	1089			GI Extreme Cold Weather Bunny Boots
13	10-89 14 W	1089			GI Extreme Cold Weather Bunny Boots
14	11-10	1110			Nylon Commando Wallet
15	11-11	1111			Nylon Commando Wallet
16	11-14	1114			Nylon Commando Wallet
17	11-143	11143			Nylon Commando Wallet
18	11-147	11147			Nylon Commando Wallet
19	11-15	1115			Nylon Commando Wallet
20	11-16	1116			Nylon Commando Wallet
21	12-01	1201			Pepper Sprays Gel Sabre Red
22	12-01F	1201F		1	Fire Master Fog Pepper Spray
23	12-02	1202			Pepper Sprays Gel Sabre Red
24	12-025	12025			Pepper Sprays Gel Sabre Red
25	12-027	12027			Pepper Sprays Gel Sabre Red
26	12-028	12028			Pepper Sprays Gel Sabre Red
27	12-02F	1202F		1	Key Chain Pepper Spray
28	12-03	1203			Pepper Sprays Gel Sabre Red
29	12-031F	12031F			Cop Top Fog Cone Pepper Spray
30	12-032F	12032F			Flip Top Stream Pepper Spray
31	12-033F	12033F			Flip Top Cop Med Fog Cone Pepper Spray
32	12-035	12035			Pepper Sprays Gel Sabre Red
33	12-03F	1203F			Cop Top Stream Pepper Spray
34	12-04	1204			Pepper Sprays Gel Sabre Red
35	12-041F	12041F			Cop Top Fog Cone Pepper Spray
36	12-042F	12042F			Flip Top Stream Pepper Spray
37	12-043F	12043F			Flip Top Cop Med Fog Cone Pepper Spray
38	12-04F	1204F			Cop Top Stream Pepper Spray
39	12-05	1205			Pepper Sprays Gel Sabre Red
40	12-055	12055			Pepper Sprays Gel Sabre Red
41	12-06	1206		1	Police Magnum Sabre Red
Fox Combinations

Cell Formulas
Range	Formula
D1	Supplier ID
D2	10-01
D3	10-012
D4	10-50 OD
D5	10-502
D6	10-505
D7	10-507
D8	10-89 04 W
D9	10-89 05 W
D10	10-89 08 R
D11	10-89 11 W
D12	10-89 14 R
D13	10-89 14 W
D14	11-10
D15	11-11
D16	11-14
D17	11-143
D18	11-147
D19	11-15
D20	11-16
D21	12-01
D22	12-01F
D23	12-02
D24	12-025
D25	12-027
D26	12-028
D27	12-02F
D28	12-03
D29	12-031F
D30	12-032F
D31	12-033F
D32	12-035
D33	12-03F
D34	12-04
D35	12-041F
D36	12-042F
D37	12-043F
D38	12-04F
D39	12-05
D40	12-055
D41	12-06
E1	Reference No.
E2	1001
E3	10012
E4	1050
E5	10502
E6	10505
E7	10507
E8	1089
E9	1089
E10	1089
E11	1089
E12	1089
E13	1089
E14	1110
E15	1111
E16	1114
E17	11143
E18	11147
E19	1115
E20	1116
E21	1201
E22	1201F
E23	1202
E24	12025
E25	12027
E26	12028
E27	1202F
E28	1203
E29	12031F
E30	12032F
E31	12033F
E32	12035
E33	1203F
E34	1204
E35	12041F
E36	12042F
E37	12043F
E38	1204F
E39	1205
E40	12055
E41	1206
F1	Wholesale
H1	Quantity
H2	Replacement Boot Laces
H3	Replacement Boot Laces
H4	Blousing Garters
H5	Blousing Garters
H6	Blousing Garters
H7	Blousing Garters
H8	GI Extreme Cold Weather Bunny Boots
H9	GI Extreme Cold Weather Bunny Boots
H10	GI Extreme Cold Weather Bunny Boots
H11	GI Extreme Cold Weather Bunny Boots
H12	GI Extreme Cold Weather Bunny Boots
H13	GI Extreme Cold Weather Bunny Boots
H14	Nylon Commando Wallet
H15	Nylon Commando Wallet
H16	Nylon Commando Wallet
H17	Nylon Commando Wallet
H18	Nylon Commando Wallet
H19	Nylon Commando Wallet
H20	Nylon Commando Wallet
H21	Pepper Sprays Gel Sabre Red
H22	Fire Master Fog Pepper Spray
H23	Pepper Sprays Gel Sabre Red
H24	Pepper Sprays Gel Sabre Red
H25	Pepper Sprays Gel Sabre Red
H26	Pepper Sprays Gel Sabre Red
H27	Key Chain Pepper Spray
H28	Pepper Sprays Gel Sabre Red
H29	Cop Top Fog Cone Pepper Spray
H30	Flip Top Stream Pepper Spray
H31	Flip Top Cop Med Fog Cone Pepper Spray
H32	Pepper Sprays Gel Sabre Red
H33	Cop Top Stream Pepper Spray
H34	Pepper Sprays Gel Sabre Red
H35	Cop Top Fog Cone Pepper Spray
H36	Flip Top Stream Pepper Spray
H37	Flip Top Cop Med Fog Cone Pepper Spray
H38	Cop Top Stream Pepper Spray
H39	Pepper Sprays Gel Sabre Red
H40	Pepper Sprays Gel Sabre Red
H41	Police Magnum Sabre Red
G22	1
G27	1
G41	1

I need a formula or function to remove all the rows with (1) in them in column G. Thank you in advance for any and all assistance.

 

[carmontj]

have you tried filters?

 

[hiker95]

SOLTEC,

Here is a macro solution for you to consider that does not use filtering, or, looping thru the data, but, removes the rows in column G that contain 1.

Sample raw data (not all rows are shown for brevity):

Excel 2007
	D	E	F	G	H
1	Supplier ID	Reference No.	Wholesale		Quantity
2	10-01	1001			Replacement Boot Laces
3	10-012	10012			Replacement Boot Laces
20	11-16	1116			Nylon Commando Wallet
21	12-01	1201			Pepper Sprays Gel Sabre Red
22	12-01F	1201F		1	Fire Master Fog Pepper Spray
23	12-02	1202			Pepper Sprays Gel Sabre Red
24	12-025	12025			Pepper Sprays Gel Sabre Red
25	12-027	12027			Pepper Sprays Gel Sabre Red
26	12-028	12028			Pepper Sprays Gel Sabre Red
27	12-02F	1202F		1	Key Chain Pepper Spray
28	12-03	1203			Pepper Sprays Gel Sabre Red
29	12-031F	12031F			Cop Top Fog Cone Pepper Spray
39	12-05	1205			Pepper Sprays Gel Sabre Red
40	12-055	12055			Pepper Sprays Gel Sabre Red
41	12-06	1206		1	Police Magnum Sabre Red
42
Fox Combinations

After the macro:

Excel 2007
	D	E	F	G	H
1	Supplier ID	Reference No.	Wholesale		Quantity
2	10-01	1001			Replacement Boot Laces
3	10-012	10012			Replacement Boot Laces
20	11-16	1116			Nylon Commando Wallet
21	12-01	1201			Pepper Sprays Gel Sabre Red
22	12-02	1202			Pepper Sprays Gel Sabre Red
23	12-025	12025			Pepper Sprays Gel Sabre Red
24	12-027	12027			Pepper Sprays Gel Sabre Red
25	12-028	12028			Pepper Sprays Gel Sabre Red
26	12-03	1203			Pepper Sprays Gel Sabre Red
27	12-031F	12031F			Cop Top Fog Cone Pepper Spray
37	12-05	1205			Pepper Sprays Gel Sabre Red
38	12-055	12055			Pepper Sprays Gel Sabre Red
39
40
41
42
Fox Combinations

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub DeleteColumnG_rows_EqualTo1()
' hiker95, 03/11/2015, ME841686
With Sheets("Fox Combinations")
  On Error Resume Next
  With .Range("G2:G" & CStr(.Range("D" & Rows.Count).End(xlUp).Row))
    .Replace "1", "#NAME?", xlWhole, xlByRows, False
    .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete xlUp
  End With
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the DeleteColumnG_rows_EqualTo1 macro.