Solved

HOW TO SELECT SLICER VALUES WITH VBA

Posted on 2016-11-09
3
790 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
[X]
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
3 Comments
 
LVL 51

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 51

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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
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…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

691 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