Posted on 2016-11-09
Medium Priority
Last Modified: 2016-11-09
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
Question by:Dokat
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 52

Accepted Solution

Rgonzo1971 earned 2000 total points
ID: 41881279

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


Author Closing Comment

ID: 41881615
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"?

LVL 52

Expert Comment

ID: 41881655
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


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The code described here does no longer work. Please see replacement Article: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3887-Getting-your-EE-Ranking-statistics-in-Excel-The-Next-Generation.html (http…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question