实用的VBAicon代码,For Each: 1、批量删除有错误引用的名称; 2、批量删除自定义样式; 3、批量删除批注; 4、刷新透视表; 5、刷新power query数据连接; 6、用箭头显示工作表中公式所引用的单元格; 7、拆分工作表为单独文件。。。感谢@李解Excel 老师分享:
'批量删除有错误引用的名称
Sub DeleteErrNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
If InStr(UCase(n.RefersTo), “#REF!”) Then n.Delete
Next
MsgBox “OK”
End Sub
'批量删除自定义样式
Sub DeleteCustomStyles()
Dim s As Style
For Each s In ActiveWorkbook.Styles
If Not s.BuiltIn Then s.Delete
Next
MsgBox “OK”
End Sub
'批量删除批注
Sub DeleteComments()
Dim c As Range
On Error Resume Next
For Each c In ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments)
c.ClearComments
Next
MsgBox “OK”
End Sub
'刷新透视表
Sub RefreshPivots()
Dim pv As PivotCache