我尝试过谷歌搜索并搜索这个,但无法完全理解。我想做的就是循环遍历活动工作表上的切片器并删除切片器(如果存在)。
目前我那里有 6 个切片机。以前我有
ActiveSheet.Shapes.Range(Array("Market Segment Name 2", "Line of Business 2" _
, "Customer Name", "Product Group Name", "Product Type Name", "Product Code") _
).Select
Selection.Delete
但如果我已经删除了切片器,那就不好了。
现在我正在尝试(注意 wb 在名为“Public”的模块中设置为全局变量)
Option Explicit
Dim sl As Slicer
Dim slName As String
Set wb = ActiveWorkbook
For Each sl In wb.SlicerCaches
If sl.Name = "Market Segment Name 2" Or _
sl.Name = "Line of Business 2" Or _
sl.Name = "Customer Name" Or _
sl.Name = "Product Group Name" Or _
sl.Name = "Product Type Name" Or _
sl.Name = "Product Name" Then
slName = sl.Name
ActiveSheet.Shapes.Range(slName).Delete
End If
Next sl
对我来说,它似乎应该有效。如果我进入 SlicerItem 级别,我就可以使用它,但我只是不知道如何在 Slicer 级别访问它......
任何想法将不胜感激。谢谢。
如果失败,我将尝试构建数组并以这种方式删除,但我仍然需要一种测试切片器当前是否存在的方法。
我能想到的有两种方法。
其一是Force
方法。这里我们不检查切片器是否存在。如果它存在,我们只需将其删除即可。例如
On Error Resume Next
ActiveSheet.Shapes.Range("Market Segment Name 2").Delete
ActiveSheet.Shapes.Range("Line of Business 2").Delete
'
'~~> And so on
'
On Error GoTo 0
另一种方法是实际检查切片器是否存在,然后将其删除。例如
Dim sl As SlicerCache
On Error Resume Next
Set sl = ActiveWorkbook.SlicerCaches("Market Segment Name 2")
On Error GoTo 0
If Not sl Is Nothing Then sl.Delete
'For Each sl In ActiveWorkbook.SlicerCaches
'Debug.Print sl.Name
'Next
EDIT:
评论跟进。
将第一个代码转换为循环。
Sub Sample()
Dim sSlicers As String
Dim Myar
Dim i As Long
'~~> Slicers you want to delete
sSlicers = "Market Segment Name 2,Line of Business 2"
sSlicers = sSlicers & "," & "Customer Name,Product Group Name"
sSlicers = sSlicers & "," & "Product Type Name,Product Code"
'~~> Split the names using "," as a delimiter
'~~> If your slicer names have "," then use a different delimiter
Myar = Split(sSlicers, ",")
For i = LBound(Myar) To UBound(Myar)
On Error Resume Next
ActiveSheet.Shapes.Range(Myar(i)).Delete
On Error GoTo 0
Next i
End Sub
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)