标签 Excel 下的文章

Sub 选中单列去重()
'
'选中单列去重 宏
'

'选中单列，去重删除整行，单独写一个不知道能不能提高效率

Dim i, j, rng As Range, dict As Object, fr, lr, res
'获取选中区域中已使用的区域
Set rng = Intersect(ActiveSheet.UsedRange, Selection)
fr = rng.Row
lr = fr + rng.Rows.Count - 1
'引用勾选：VBA窗体-工具-引用-勾选“Microsoft Scripting Runtime”
'生成字典
Set dict = CreateObject("scripting.dictionary")
For i = lr To fr Step -1
    res = CStr(Cells(i, j).Value)
    '如果字典中不存在键res则加入键res并赋值为""，否则删除当前行
    If Not dict.exists(res) Then dict(res) = "" Else Rows(i).Delete
Next

End Sub

Sub 选中区域去重()
'
'选中区域去重 宏
'

'可以选中单列或多列，去重删除整行

Dim i, j, rng As Range, dict As Object, fr, lr, fc, lc, res
'获取选中区域中已使用的区域
Set rng = Intersect(ActiveSheet.UsedRange, Selection)
fr = rng.Row
lr = fr + rng.Rows.Count - 1
fc = rng.Column
lc = fc + rng.Columns.Count - 1
'引用勾选：VBA窗体-工具-引用-勾选“Microsoft Scripting Runtime”
'生成字典
Set dict = CreateObject("scripting.dictionary")
For i = lr To fr Step -1
    res = ""
    For j = fc To lc
        res = res & CStr(Cells(i, j).Value)
    Next
    '如果字典中不存在键res则加入键res并赋值为""，否则删除当前行
    If Not dict.exists(res) Then dict(res) = "" Else Rows(i).Delete
Next

End Sub

Sub 删除包含指定内容的行()
'在工作中经常遇到需要筛选出不包含指定内容的数据行
' 删除包含指定内容的行 宏
'用这个宏可以解决问题

'sheet2内A列保存指定的内容，I1单元格保存指定内容的行数
'J1单元格保存需要操作的数据行数+2
'sheet1中B列用来对比，第一行为空行，第二行为标题行
'从第三行开始操作，操作后第一行一定会被删除
'对比当前单元格和指定内容有相同的就标记此行，最后一起删除。

Dim tm, im, jm, numx, delrng As Range, jmi, i, j, dict As Object
tm = Timer
numx = 0
jmi = 0
Sheets("Sheet2").Select
im = Cells(1, 9).Value
jm = Cells(1, 10).Value
'引用勾选：VBA窗体-工具-引用-勾选“Microsoft Scripting Runtime”
'生成字典
Set dict = CreateObject("scripting.dictionary")
For i = 1 To im
    dict(CStr(Cells(i, 1).Value)) = ""
Next
Sheets("Sheet1").Select
'设置标记区域为第一行
Set delrng = Rows(1)
Dim js
For j = 3 To jm
    '如果当前单元格存在于字典中
    If dict.exists(CStr(Cells(j, 2).Value)) Then
        '将当前行加入标记区域
        Set delrng = Union(delrng, Rows(j))
        numx = numx + 1
        jmi = jmi + 1
    End If
Next
delrng.Delete
With Sheet2
    .Cells(1, 10).Value = jm - jmi
    .Cells(1, 2).Value = "总共删除了" & numx & "行"
    .Cells(2, 3).Value = "用时：" & Format(Timer - tm, "0.00") & "秒"
End With

End Sub