• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4720
  • Last Modified:


I have a command button that makes the selection for the pivot table. I use below VBA code to make the selection. However it takes too long to run. is there a way to optimize the code where it doesnt go thru each line...clears all filters and selects where SlicerItems("").Selected=True

Sub HenkelOnly()
' HenkelOnly Macro

    With ActiveWorkbook.SlicerCaches("Slicer_Manufacturer1")
        .SlicerItems("HENKEL").Selected = True
        .SlicerItems("3M").Selected = False
        .SlicerItems("ADVANCED BEAUTY CARE INC").Selected = False
        .SlicerItems("ADVANCED BEAUTY INC").Selected = False
        .SlicerItems("AGILITY SPORTS").Selected = False
        .SlicerItems("ALAMO MANUEL MARTIARENA").Selected = False
        .SlicerItems("ALEN USA").Selected = False
        .SlicerItems("AMERICAN SAFETY RAZOR").Selected = False
        .SlicerItems("AMPRO INDUSTRIES").Selected = False
        .SlicerItems("ANIKA LABORATORIES").Selected = False
        .SlicerItems("AP DEAUVILLE").Selected = False
        .SlicerItems("APOLLO").Selected = False
        .SlicerItems("ASPEN BRANDS").Selected = False
        .SlicerItems("AWESOME PRODUCTS").Selected = False
        .SlicerItems("B&G FOODS INC").Selected = False
        .SlicerItems("BEIERSDORF").Selected = False
        .SlicerItems("BELLA CURLS").Selected = False
        .SlicerItems("BIOCARE LABS").Selected = False
        .SlicerItems("BLISTEX").Selected = False
        .SlicerItems("BRIGHT AIR").Selected = False
        .SlicerItems("BRONNER BROS").Selected = False
        .SlicerItems("BURMAX CO").Selected = False
        .SlicerItems("CAITCO").Selected = False
        .SlicerItems("CALIFORNIA SCENTS").Selected = False
        .SlicerItems("CARMA LABORATORIES").Selected = False
        .SlicerItems("CHATTEM").Selected = False
        .SlicerItems("CHEATHAM CHEMICAL CO.").Selected = False
        .SlicerItems("CHEMTURA CORPORATION").Selected = False
        .SlicerItems("CHIN HO SHING (USA)").Selected = False
        .SlicerItems("CHURCH & DWIGHT").Selected = False
        .SlicerItems("COLGATE-PALMOLIVE").Selected = False
        .SlicerItems("CONAIR CORP").Selected = False
        .SlicerItems("CONTROL BRAND").Selected = False
        .SlicerItems("CR BRANDS").Selected = False
        .SlicerItems("DAGETT & RAMSDELL").Selected = False
        .SlicerItems("DAMPRID").Selected = False
        .SlicerItems("DERMASIL LABS").Selected = False
        .SlicerItems("DR MIRACLES LLC").Selected = False
        .SlicerItems("E T BROWNE DRUG CO INC").Selected = False
        .SlicerItems("ECOCO INC").Selected = False
        .SlicerItems("ELITE ESSENTIALS").Selected = False
        .SlicerItems("ENERGIZER").Selected = False
        .SlicerItems("ENPER INC").Selected = False
        .SlicerItems("EOS PRODUCTS").Selected = False
        .SlicerItems("FANTASIA INDUSTRIES CORP").Selected = False
        .SlicerItems("FAULTLESS STARCH / BON AMI COMPANY").Selected = False
        .SlicerItems("FREEMAN BEAUTY LABS").Selected = False
        .SlicerItems("FRUIT OF THE EARTH").Selected = False
        .SlicerItems("GENOMMA LAB").Selected = False
        .SlicerItems("GOJO INDUSTRIES").Selected = False
        .SlicerItems("GROWORKS").Selected = False
        .SlicerItems("HAIN CELESTIAL GROUP").Selected = False
        .SlicerItems("HBC CHEMICAL CO.").Selected = False
        .SlicerItems("HELEN OF TROY").Selected = False
        .SlicerItems("HIGH RIDGE").Selected = False
        .SlicerItems("HIGH TIME PRODUCTS").Selected = False
        .SlicerItems("HILLSHIRE BRANDS").Selected = False
        .SlicerItems("HOLLYWOOD BEAUTY IMPORTS CO").Selected = False
        .SlicerItems("HOYU AMERICAN CO").Selected = False
        .SlicerItems("ICONIC").Selected = False
        .SlicerItems("ILEX CONSUMER PRODUCTS").Selected = False
        .SlicerItems("IMPERIAL DAX CO").Selected = False
        .SlicerItems("INSPIRED BEAUTY BRANDS").Selected = False
        .SlicerItems("J M PRODUCTS").Selected = False
        .SlicerItems("J STRICKLAND CO.").Selected = False
        .SlicerItems("JELMAR").Selected = False
        .SlicerItems("JOHNSON & JOHNSON").Selected = False
        .SlicerItems("KAO").Selected = False
        .SlicerItems("KEY BRANDS").Selected = False
        .SlicerItems("KEYSTONE LABORATORIES INC").Selected = False
        .SlicerItems("LA CORONA COMPANY").Selected = False
        .SlicerItems("LANE BRYANT").Selected = False
        .SlicerItems("LARRY CHERNILA").Selected = False
        .SlicerItems("LAVO").Selected = False
        .SlicerItems("L'OREAL").Selected = False
        .SlicerItems("L'OREAL SSC").Selected = False
        .SlicerItems("LORNAMEAD GROUP").Selected = False
        .SlicerItems("LUSTER PRODUCTS INC.").Selected = False
        .SlicerItems("M & M PRODUCTS").Selected = False
        .SlicerItems("M30 MANUFACTURING").Selected = False
        .SlicerItems("MACANDREWS FORBES HOLDINGS INC").Selected = False
        .SlicerItems("MAID BRANDS").Selected = False
        .SlicerItems("MENTHOLATUM COMPANY").Selected = False
        .SlicerItems("MERCK").Selected = False
        .SlicerItems("MURRAY S WORLDWIDE INC.").Selected = False
        .SlicerItems("NAKOMA PRODUCTS").Selected = False
        .SlicerItems("NAMASTE LABORATORIES LLC").Selected = False
        .SlicerItems("NATURE'S PROTEIN INC").Selected = False
        .SlicerItems("NEUTRLAB").Selected = False
        .SlicerItems("NEW SUNSHINE").Selected = False
        .SlicerItems("NEWHALL LABORATORIES").Selected = False
        .SlicerItems("NO-AD PRODUCTS").Selected = False
        .SlicerItems("NOVARTIS PHARMACEUTICALS").Selected = False
        .SlicerItems("O'KEEFFE'S COMPANY").Selected = False
        .SlicerItems("OLD WILLIAMSBURGH CANDLE").Selected = False
        .SlicerItems("PANAMA JACK PRODUCTS").Selected = False
        .SlicerItems("PARA LABORATORIES").Selected = False
        .SlicerItems("PARAMOUNT CHEMICAL SPECIALTIES INC").Selected = False
        .SlicerItems("PARFUMS DE COEUR").Selected = False
        .SlicerItems("PDC BRANDS").Selected = False
        .SlicerItems("PEPSICO").Selected = False
        .SlicerItems("PERFECT PURITY").Selected = False
        .SlicerItems("PERSONAL CARE").Selected = False
        .SlicerItems("PF HARRIS COMPANY").Selected = False
        .SlicerItems("PHOENIX BRANDS").Selected = False
        .SlicerItems("PIC CORP").Selected = False
        .SlicerItems("PRESTIGE BRANDS").Selected = False
        .SlicerItems("PRIVATE LABEL").Selected = False
        .SlicerItems("PROCTER & GAMBLE").Selected = False
        .SlicerItems("PROFESSIONAL PRODUCTS UNLIMTED").Selected = False
        .SlicerItems("PRO-LINE INTERNATIONAL").Selected = False
        .SlicerItems("RCJP ACQUISITION INC").Selected = False
        .SlicerItems("RECKITT BENCKISER").Selected = False
        .SlicerItems("REDDING HAIR CARE").Selected = False
        .SlicerItems("REDEX INDUSTRIES").Selected = False
        .SlicerItems("REDOX BRANDS").Selected = False
        .SlicerItems("RENPURE").Selected = False
        .SlicerItems("REVLON").Selected = False
        .SlicerItems("ROTO-ROOTER").Selected = False
        .SlicerItems("RUG DOCTOR").Selected = False
        .SlicerItems("SC JOHNSON").Selected = False
        .SlicerItems("SCIENCE OF BEAUTY LLC").Selected = False
        .SlicerItems("SCOTCH CORPORATION").Selected = False
        .SlicerItems("SERVAAS LABORATORIES").Selected = False
        .SlicerItems("SKINNUTRITIONS").Selected = False
        .SlicerItems("SOFTEE").Selected = False
        .SlicerItems("SPARTAN BRANDS INC").Selected = False
        .SlicerItems("SPECTRUM BRANDS").Selected = False
        .SlicerItems("SPRAYWAY INC").Selected = False
        .SlicerItems("STRAIGHT ARROW PRODUCTS").Selected = False
        .SlicerItems("STRENGTH OF NATURE").Selected = False
        .SlicerItems("SUN AND SKIN CARE RESEARCH").Selected = False
        .SlicerItems("SUN PRODUCTS").Selected = False
        .SlicerItems("SUNDIAL BRANDS").Selected = False
        .SlicerItems("SUNSHINE MAKERS").Selected = False
        .SlicerItems("SUREFIL").Selected = False
        .SlicerItems("SWIZCO").Selected = False
        .SlicerItems("THE CLOROX COMPANY").Selected = False
        .SlicerItems("THE COLOMER").Selected = False
        .SlicerItems("THE COLOMER GROUP").Selected = False
        .SlicerItems("ULTIMARK PRODUCTS LLC").Selected = False
        .SlicerItems("ULTRA GLOW").Selected = False
        .SlicerItems("UNILEVER").Selected = False
        .SlicerItems("UNIVERSAL BEAUTY PRODUCTS").Selected = False
        .SlicerItems("VALEANT CONSUMER PRODUCTS").Selected = False
        .SlicerItems("VI-JON LABORATORIES").Selected = False
        .SlicerItems("WAHL CLIPPER CORPORATION").Selected = False
        .SlicerItems("WALT DISNEY").Selected = False
        .SlicerItems("WAVE LIFE LLC").Selected = False
        .SlicerItems("WD-40 COMPANY").Selected = False
        .SlicerItems("WEIMAN PRODUCTS").Selected = False
        .SlicerItems("WILLERT HOME PRODUCTS").Selected = False
        .SlicerItems("WIPRO TECHNOLOGIES").Selected = False
        .SlicerItems("(blank)").Selected = False
    End With
  • 2
1 Solution

no but you can have a shorter code
With ActiveWorkbook.SlicerCaches("Slicer_Manufacturer1")
    For Each oSlicerItem In .SlicerItems
        If oSlicerItem.Name = "HENKEL" Then
            oSlicerItem.Selected = False
            oSlicerItem.Selected = True
        End If
    Next oSlicerItem
End With

Open in new window

DokatAuthor Commented:
Thank you so much for your help!

I noticed if i have other selection already made...code doesnt work. If i dont have any selections on any of the 8 slicers then it works. Is there a way around this where it first clears all slicer selections then selects "Henkel"?

then try
For Each slcCache in ActiveWorkbook.SlicerCaches

With ActiveWorkbook.SlicerCaches("Slicer_Manufacturer1")
    For Each oSlicerItem In .SlicerItems
        If oSlicerItem.Name = "HENKEL" Then
            oSlicerItem.Selected = False
            oSlicerItem.Selected = True
        End If
    Next oSlicerItem
End With

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now