返回目录:excel表格制作
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,运行代码,后返回工作表,会发现单元格中的重复字符已经被去答掉。
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
在该工作表标签上点百击鼠标右键,输入上述代度码,运行该代码或运行宏“删问除重复行”即可。有个缺答陷,只是判断图内号相同即删除,假如图号相同、数量不同的行容照样删除。
数据百-高级筛选-区域中选择要筛选的单元区域-下面有度勾选-不重复-确定
能不能用VBA实现呢内? 因为有很多,想用个简单的方法,谢谢啊!
回答你,这挺简单的
用下面代容码
Sub Slt() '不重复值
Dim dic As Object
Dim arr, k%
Set dic = CreateObject("Scripting.Dictionary")
arr = Range("A1", [A65536].End(3))
For k = 1 To UBound(arr)
dic(arr(k, 1)) = ""
Next
Range("a1:k1000").Clear
arr = dic.keys
[a1].Resize(dic.Count) = Application.Transpose(arr)
dic.RemoveAll
Erase arr
End Sub
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、然后就可答以删除重复数据的整行。