Solved

HOW TO SELECT SLICER VALUES WITH VBA

Posted on 2016-11-09
3
24 Views
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
'

'
    ActiveWorkbook.SlicerCaches("Slicer_Manufacturer1").ClearManualFilter
    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
0
Comment
Question by:Dokat
  • 2
3 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41881279
Hi,

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

Open in new window

Regards
0
 

Author Closing Comment

by:Dokat
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"?

Thanks
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41881655
then try
For Each slcCache in ActiveWorkbook.SlicerCaches
        slcCache.ClearManualFilter
Next

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

Open in new window

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now