Excel的VBA多个sheet数据拆分与批注提取及数据列去重复

1 Excel的所有文本内容进行合并去重

1.1未进行去重前效果

1.2 VBA代码去重数据列

Sub a()
Dim d As Object
Set d = CreateObject(“Scripting.Dictionary”)
Dim rng As Range
For Each rng In [a1:ek35] '数据源
If rng <> “” Then d(rng.Value) = “”
Next
[c1].Resize(d.Count) = Application.Transpose(d.keys)
Set d = Nothing
End Sub

1.3 VBA去重后效果

——————————————————————————————————————————————————————

2 Excel的多个sheet拆分成单个sheet文件

2.1 未拆分前sheet

2.2 拆分sheet成独立文件的VBA

Sub sheet转独立文件()
Dim sht As Worksheet
Application.ScreenUpdating = False
ipath = ThisWorkbook.Path & "\"
For Each sht In Sheets
sht.Copy
ActiveWorkbook.SaveAs ipath & sht.Name
ActiveWorkbook.Close
Next
Application.ScreenUpdating = True
End Sub

2.3 拆分后sheet的文件效果

——————————————————————————————————————————————————

3 Excel批注拆分

批注示例如下：

VBA程序代码构建Excel自定义函数

Function cmt(X As Range)
Application.Volatile True '自定义函数自动刷新
cmt = X.Cells.Comment.Text '提取批注文本
End Function

使用VBA构建的自定义函数提取批注

直接使用cmt进行判断，外加if判断ISBLANK(P15)空格情况