返回目录:excel表格制作
'利用字典去重,下面是示例代码,去除第一列的重复项Sub Test() '利用字典去重,字典的7a686964616fe59b9ee7ad94363特性是key值不能重复
Dim Dic, Arr
Dim i As Integer, r As Integer
Dim Str As String
r = Sheet1.Range("A65536").End(xlUp).Row
If r = 1 Then Exit Sub '如果第一列没有数据那么退出程序
Set Dic = CreateObject("scripting.dictionary") '创建字典对象
For i = 1 To r '将第一列数据添加到字典的key值中
Dic(CStr(Cells(i, 1))) = ""
Next
Arr = Dic.keys '返回字典key的数组
Set Dic = Nothing '销毁对象
Str = Join(Arr, ",") '将数组中的内容显示为一字符串
MsgBox Str
End Sub
1、打开要进行数据处理知的表格,
2、按道ALT+f11,进入VBE程序操作页面,
3、将内鼠标下移,右键弹出如图,
4、选择插入-模块,
5、鼠标向右移动,将如下代码写入:
Sub test()
Dim d As Object
Dim r%, i%
Dim arr
Set d = CreateObject("scripting.dictionary")
With Worksheets("sheet1")
r = .Cells(.Rows.Count, 3).End(xlUp).Row
arr = .Range("c2:d" & r)
For i = 1 To UBound(arr)
d.RemoveAll
For j = 1 To Len(arr(i, 1))
ch = Mid(arr(i, 1), j, 1)
d(ch) = ""
Next
arr(i, 2) = Join(d.Keys, "")
Next
.Range("d2").Resize(UBound(arr), 1) = Application.Index(arr, 0, 2)
End With
End Sub
6、按F5,运行代码,后返回工作表容,会发现单元格中的重复字符已经被去掉。
1、首先打开需要编辑的来Excel表格,右键单击工作表的标签,选择打开“查看代码”。
2、然后在弹出来的窗口中点自击输入:
Sub 删除重复行()
Dim xRow As Integer
Dim i As Integer
xRow = Range("B65536").End(xlUp).Row
For i = 2 To xRow
For j = i + 1 To xRow
If Cells(j, 2) = Cells(i, 2) Then
Range(Cells(j, 1), Cells(j, 256)).Rows.Delete
j = j - 1
xRow = xRow - 1
End If
Next
Next
End Sub
3、然后点击左上百角的保存按钮进行保存,之后运行该代码或运行宏“删除重复行”即可。度
4、然后就可以删除重复数据的整行。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address(0, 0) <> "B1" Then Exit Sub
Dim dic As Object
arr = Range([a1], [a65536].End(xlUp))
Set dic = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr, 1)
dic(arr(i, 1)) = ""
Next
[a65536].End(xlUp).Offset(-dic.Count + 1, 1).Resize(dic.Count, 1) = Application.Transpose(dic.keys)
End Sub
右键工百作度表标签问》粘答贴代内码容