excel - 如何删除这些指定的列？

Question

我想删除工作簿中所有工作表的特定列。当我运行我的代码时，它只会删除第一行中指定的列。其余不包括在内。如何使用许多列来指定？我该怎么做？

Sub Dosomething()
  Dim xSh As Worksheet
  Application.ScreenUpdating = False
  For Each xSh In Worksheets
      xSh.Select
      Call DeleteColumns
  Next
  Application.ScreenUpdating = True
End Sub
Sub DeleteColumns()
  Dim ColumnsToDelete As String, V As Variant
  ColumnsToDelete = ColumnsToDelete & "AAFA Certified|AATCC 116 Grade|AATCC 16.1-2014 Grade|AATCC 16.3-2014 Grade|AATCC 8 Grade|AC Rated|AC Rating|ACT Flammability Guidelines Compliant|ADA Compliant"
  ColumnsToDelete = ColumnsToDelete & "|" & "Additional Intended Use For Child|AHAM CADR Dust rating|AHAM CADR Pollen rating|AHAM CADR Smoke rating|AHAM Certified|AHMA Compliant|AHRI Certified|American Cornhole Association (ACA) Certified|ANSI A117.1 Compliant|ANSI A137.1 Compliant"
  ColumnsToDelete = ColumnsToDelete & "|" & "ANSI IT9.2 Compliant|ANSI Z1.5A/CGA 9.1M Certified|ANSI Z124.1.2 Compliant|ANSI Z124.3 Compliant|ANSI Z124.6-97 Compliant|ANSI Z358 Compliant|ANSI/BHMA A156 Certified|ANSI/BIFMA e3 Furniture Sustainability Standard|ANSI/BIFMA X5.1 Office Seating|ANSI/BIFMA X5.11 Large Occupant Office Seating"
  ColumnsToDelete = ColumnsToDelete & "|" & "ANSI/BIFMA X5.3 Vertical Files|ANSI/BIFMA X5.4 Lounge & Public Seating|ANSI/BIFMA X5.5 Desk Products|ANSI/BIFMA X5.6 Panel Systems|ANSI/BIFMA X5.9 Storage|ANSI/BIFMA X6.1 Educational Seating|ANSI/BIFMA X7.1 Standard for Formaldehyde & TVOC Emissions|ANSI/ESD S20.20 Certified|ANSI/NISO Z39.48 Compliant|ANSI/SOHO S6.5 Small Office/Home Office"
  ColumnsToDelete = ColumnsToDelete & "|" & "APMA Certified| ASHRAE 62.2 Standard Compliant| ASME A112.18.1 Compliant| ASME A112.18.2 Compliant| ASME A112.19.1 Compliant| ASME A112.19.2 Compliant| ASME A112.19.3 Compliant| ASP:02:01 Certification| ASSE 1001 Certified| ASSE 1006 Certified"
  ColumnsToDelete = ColumnsToDelete & "|" & "ASSE 1016 Certified| ASTM E1590-07| ASTM F1004-19 Certified| ASTM F1148-17| ASTM F1148-18 Certified| ASTM F1169 - 13 Certified| ASTM F1821 - 16 Certified| ASTM F1912-17 Certified| ASTM F1917 - 12 Certified| ASTM F2057 - 14 Certified"
  ColumnsToDelete = ColumnsToDelete & "|" & "ASTM F2057 - 17 Certified| ASTM F2057 - 19 Certified| ASTM F2085 - 12 Certified| ASTM F2088-15| ASTM F2194 Certified| ASTM F2225-15| ASTM F2388 - 18 Certified| ASTM F2613 - 17 Certified| ASTM F2933-18 Certified| ASTM F3084 - 16 Certified"
  ColumnsToDelete = ColumnsToDelete & "|" & "ASTM F3096 - 14 Certified| ASTM F3317-18 Certified| ASTM F381-16| ASTM F406 - 10a Certified| ASTM F406 - 15 Certified| ASTM F406 - 17 Certified| ASTM F963 - 17 Certified| ASTM F963-11| ATA Rated| AZO Free"
  ColumnsToDelete = ColumnsToDelete & "|" & "BHFTI Upholstered Furniture Flammability Compliant| BIFMA G1 Ergonomics Guideline for Furniture| BIFMA HCF 8.1 - Healthcare Furniture Design| Blauer Engel| BPA Free| BS 5287: 1988 Certified| BS 5852 Certified| BS 5867-2:2008 Certified| BS 7176: 2007 Certified| BS 7177:2008+A1:2011 Certified"
  ColumnsToDelete = ColumnsToDelete & "|" & "BS EN 13120: 2009 + A1: 2014 (Requirements for Window Blinds or Drapery Cords) - EU ONLY| BS EN 71 Compliant - EU ONLY| BS EN 716-1 Compliant - EU ONLY| BS EN12790 Certified| Bulb Included| Bulb Type| CAL TB 116 Compliant| CAL TB 117-2013 Compliant| CALGreen Compliant| California AB 1953 Compliant"
  ColumnsToDelete = ColumnsToDelete & "|" & "California Pesticide (DPR) Registration Number| California Proposition 65 Warning Required| Canada Product Restriction| CARB Phase II Compliant (formaldehyde emissions)| CE Certified| CEE Rating| Certifications| CertiPUR-US Certified| cETL Certified| cETL Listed"
  ColumnsToDelete = ColumnsToDelete & "|" & "CFC Free| Chemicals of Concern Compliant| Children’s Product Certificate (CPC)| CISPR 22 Compliant| Commercial ONLY Certifications| Commercial OR Residential Certifications| Commercial Warranty| Composite Wood Product (CWP)| Consumer Safety Specification for Bunk Beds ASTM F1427 - 13| Contains Flame Retardant Materials"
  ColumnsToDelete = ColumnsToDelete & "|" & "Country of Origin| Country of Origin - Additional Details| CPAI-84 Compliant| CPG Compliant| CPSC - 15 U.S. Code § 1278a - Children’s Products Containing Lead| CPSC - 16 CFR 1213 Compliant| CPSC - 16 CFR 1217 Compliant| CPSC - 16 CFR 1218 Compliant| CPSC - 16 CFR 1219 Compliant| CPSC - 16 CFR 1220 Compliant"
  ColumnsToDelete = ColumnsToDelete & "|" & "CPSC - 16 CFR 1234 Compliant| CPSC - 16 CFR 1237 Compliant| CPSC - 16 CFR 1238 Compliant| CPSC - 16 CFR 1239 Compliant| CPSC - 16 CFR 1303 Compliant| CPSC - 16 CFR 1500.49 Compliant| CPSC - 16 CFR 1513 Compliant| CPSC - 16 CFR 1630 Compliant| CPSC - 16 CFR 1631 Compliant| CPSC - 16 CFR 1632 Compliant"
  ColumnsToDelete = ColumnsToDelete & "|" & "CPSC - 16 CFR 1633 Compliant| CPSC - 16 CFR Part 1130-Requirements For Consumer Registration Of Durable Infant Or Toddler Products| CPSC - 16 CFR Part 1307-Prohibition Of Children's Toys And Child Care Articles Containing Specified Phthalates| CPSIA Compliant| CPSIA or CPSC Compliant| Cradle to Cradle Certified| CRI Certified| CRI Green Label Certified| CRI Green Label Plus Certified| CSA B125 Certified"
  ColumnsToDelete = ColumnsToDelete & "|" & "CSA B125.1 Certified| CSA B125.2 Certified| CSA B13 Certified| CSA B45 Certified| CSA B45.1 Certified| CSA B45.2 Certified| CSA Certified| CSA Listed for Canada| CSA Listed for USA| cUL Listed"
  ColumnsToDelete = ColumnsToDelete & "|" & "cUPC Certified| Dark Sky Compliant| DECT 6.0 Compliant| DIN EN 12935 Certified| Dry, Damp, or Wet Location Listed - CAN| Dry, Damp, or Wet Location Listed - USA| EEP Compliant| EIA Compliant| EISA 2007 Compliant| EISA Compliant"
  ColumnsToDelete = ColumnsToDelete & "|" & "Emergency Feed| EN 1130-1:1996 (Safety Requirements for Cribs and Cradles) - EU ONLY| EN 12221-1 (Standards for Changing Tables) - EU ONLY| EN 13219 (Standards for Trampolines) - EU ONLY| EN 14878: 2007 (Requirements for Children's Sleepwear) - EU ONLY| EN 14988-1 (Safety Standards for High Chairs) - EU ONLY| EN 15312 (Safety Standards for Multi-Sport Equipment) - EU ONLY| EN 1725 (Safety Standards for Adult Beds) - EU ONLY| EN 1930 (Standards for Child Gates and Safety Barriers) - EU ONLY| EN 50291-1 (Standards for Smoke Detectors and CO Detectors) - EU ONLY"
  ColumnsToDelete = ColumnsToDelete & "|" & "EN 54-12 (Standards for Smoke Detectors and CO Detectors) - EU ONLY| EN 71 Compliant| EN 71-14:2018 Compliant| EN 747-1 (Standards for Childrens Bunk Beds) - EU ONLY| EN 748 (Safety Standards for Football Goals) - EU ONLY| Energy Label Type| Energy Policy Act 1992 Compliant| Energy Star Compliant| EPA Compliant| EPA Pesticide Registration Number"
  ColumnsToDelete = ColumnsToDelete & "|" & "EPAct 2005 Compliant| EPP Compliant| ETL Listed| EuroLatex ECO-Standard| FCC Compliant| FCC Part 15 Certified| FCC Part 15 Compliant| FCC Part 68 Compliant| FDA Approved| FIRA Certified"
  ColumnsToDelete = ColumnsToDelete & "|" & "Fire Rated| Fire Resistant| FISP Certified| FloorScore Certification| FM Approved| FourB Compliant| FSC Certified| General Certificate of Conformity (GCC)| Glass Doors and Enclosures Regulations SOR/2016-174| Glazed Ceramics and Glassware Regulations SOR/2016-175"
  ColumnsToDelete = ColumnsToDelete & "|" & "GOLS 3.0 (Global Organic Latex Standard) Certified| GOLS License Expiry Date| GOLS License Number| Goodweave Certified| GOTS 5.0 (Global Organic Textile Standard) Certified| GOTS License Expiry Date| GOTS License Number| Grade of GOLS Certification| Grade of GOTS Certification| Green Compliance"
  ColumnsToDelete = ColumnsToDelete & "|" & "GREENGUARD Certified| GREENGUARD Gold Certified| GS (Geprüfte Sicherheit) verzeichnet| GSA Approved| Health Canada - SOR/2016-176 Compliant| Health Canada - SOR/2016-179 Compliant| Health Canada - SOR/2016-183 Compliant| Health Canada Compliant| HFES 100 Certified Ergonomic| HVI 2100 Certified"
  ColumnsToDelete = ColumnsToDelete & "|" & "HVI Certified| IAPMO Certified| IATA Compliant| Indoor Air Quality Certified| IP Rating| IPEMA Certified| ISO 14000 Certified| ISO 14001 Certified| ISO 9000 Certified| ISO 9001 Certified"
  ColumnsToDelete = ColumnsToDelete & "|" & "ITSF Compliant| ITTF Approved| ITTO Compliant| Janka Hardness Rating (PSI)| JPMA Certified| Kettles Regulations SOR/2016-181| Knife Included| Lacey Act Compliant| Latex Free| Lead Free"
  ColumnsToDelete = ColumnsToDelete & "|" & "LEED Project Appropriate| LM-79 Certified| LM-80 Certified| Low Lead Compliant| Mattress Flammability Testing - Public Occupancy Use| Maximum Wattage (per bulb)| MERV Rating| MET Listed| Minnesota 325F.177 Compliant| MOHS - Surface Hardness"
  ColumnsToDelete = ColumnsToDelete & "|" & "NAECA Certified| NAHB Compliant| NEMA Certified| NFPA 253 or ASTM E648 Certified| NFPA Compliant| NFSI Certified| NHPA Approved| NOMITE Certified| Non-Toxic| NRPA Certified"
  ColumnsToDelete = ColumnsToDelete & "|" & "NSF Certified| NSF/ANSI 184 Certified| NSF/ANSI 3 Certified| NSF/ANSI 336 Compliant| NSF/ANSI 61 Certified| OEKO-TEX Standard 100 Certified| OSHA Compliant| PAT Tested| PEFC Certified| PEI Rated"
  ColumnsToDelete = ColumnsToDelete & "|" & "PEI Rating| PFOA Free| Phthalate Free| Product Warranty| PTFE Free| PTFE or PFOA| PTFE or PFOA Free| Reason for Restriction| Responsible Down Standard Certified| RoHS Compliant"
  ColumnsToDelete = ColumnsToDelete & "|" & "SATRA Approved| SCC Compliant| SCS Certified| SDWA Compliant| SFI Certified| SOR/2016-152 - Canadian Regulation - Cribs, Cradles and Bassinets| SOR/2016-193 - Surface Coating Materials Regulations| SOR/2018-83 -Consumer Products Containing Lead Regulations| Stiftung Warentest Note| TAA Compliant"
  ColumnsToDelete = ColumnsToDelete & "|" & "TB 117-2013 Compliant| TB 133 Compliant| TCLP Compliant| Textile Flammability Regulations SOR/2016-194| Textile Labelling and Advertising Regulations (C.R.C., c. 1551)| Tipover Restraint Device Included| Title 20 - California Code of Regulations| Title 24 Compliant| Total Recycled Content (Percentage)| Total Wattage"
  ColumnsToDelete = ColumnsToDelete & "|" & "Toys Regulations SOR/2016-195| TSA Compliant| TSCA Title VI Compliant (formaldehyde emissions)| TÜV Certified| TÜV Rheinland zertifiziert| UL 10B Compliant| UL 10C Compliant| UL 1678 Listed| UL 1951 Listed| UL 2108 Listed"
  ColumnsToDelete = ColumnsToDelete & "|" & "UL 4041 - Safety Standard for Outdoor Furniture| UL Listed| ULC ORD-C627.1-2008 - Unvented Ethyl Alcohol Fuel Burning Decorative Appliances| ULC/ORD-C627.1-2008| Uniform Packaging and Labeling Regulations (UPLR) Compliant| UPC Certified| USATT Approved| USDA Compliant| USDA Organic/ NOP Certified| USDA/NOP Certificate Number"
  ColumnsToDelete = ColumnsToDelete & "|" & "USDA/NOP License Expiry Date| USPS Approved| Vermont Act 193 Compliant| VESA Compliant| Washington State VIAQ Code Compliant| Wattage| WEEE Recycling Required| WQA Gold Seal Certified| WW-P-541 Certified"
  On Error Resume Next
  For Each V In Split(ColumnsToDelete, "|")
    Rows(1).Find(Trim(V), , , xlWhole, , , False).EntireColumn.Delete
  Next
  On Error GoTo 0
End Sub

标签： excelvbaexcel-formula