VBA - Loop through slicer but exclude blank

[kialexander]

Hi,

I am using the following code to loop through all items in a slicer and then do a set of actions:

Dim sI As SlicerItem, sI2 As SlicerItem, sC As SlicerCache


Set sC = ActiveWorkbook.SlicerCaches("Slicer_Rep_Name")


With sC


For Each sI In sC.SlicerItems


sC.ClearManualFilter


For Each sI2 In sC.SlicerItems


If sI.Name = sI2.Name Then sI2.Selected = True Else: sI2.Selected = False


Next


Is there anyway that I can get it to exclude (blank)?

Thanks,

Kirstie

 

[Worf]

Hi

The example below shows how to reference blank items:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim sc1 As SlicerCache, sc2 As SlicerCache, si1 As SlicerItem, blk$
Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Categories")
Set sc2 = ThisWorkbook.SlicerCaches(4)
blk = "(vazio)"                                         ' Portuguese
'blk = "(blank)"                                        ' English
Application.ScreenUpdating = False
Application.EnableEvents = False
sc2.ClearManualFilter
For Each si1 In sc1.SlicerItems
    sc2.SlicerItems(si1.Name).Selected = si1.Selected
Next
sc2.SlicerItems(blk).Selected = False                   ' blanks
MsgBox "Update Complete"
clean_up:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
err_handle:
    MsgBox Err.Description
    Resume clean_up
End Sub