|
VBA删除重复项,只是不是很完善,得麻烦你多点几次,直到没有重复项哦,点出来的快乐,哈哈........
Sub 删除重复()
Dim Lrow As Long
Dim I As Integer
Dim myCount
Lrow = Sheets("sheet1").[A65536].End(xlUp).Row
For I = 2 To Lrow
myCount = Application.CountIf(Sheet1.Range("A2:A" & Lrow), Sheets("sheet1").Cells(I, 1))
If myCount > 1 Then
Sheets("sheet1").Cells(I, 1).Delete
End If
Next I
End Sub
下面是完善后的代码:
Sub 删除重复()
Dim Lrow As Long
Dim I As Integer
Dim J As Integer
Dim myCount
'关闭刷屏
Application.ScreenUpdating = False
'得到数据总行数
Lrow = Sheets("sheet1").[A65536].End(xlUp).Row
'避开标题行,即从第二行到最后一行进行循环
For I = 2 To Lrow
'因为考虑到进行删除操作后,需重更新得到数据总行数
Lrow = Sheets("sheet1").[A65536].End(xlUp).Row
'重第二行向新得到的总行数进行循环
For J = 2 To Lrow
'按遍历单元格条件进行计数
myCount = Application.CountIf(Sheet1.Range("A2:A" & Lrow), Sheets("sheet1").Cells(I, 1))
'计数大于1,对所在行进行删除操作
If myCount > 1 Then
Sheets("sheet1").Cells(I, 1).Delete
End If
Next J
Next I
'打开刷屏
Application.ScreenUpdating = True
End Sub
'================================================
'删除重复项第三段代码
'================================================
'受小妖版主技巧操作的启发,做出第三段代码,在此对小妖版主表示感谢!
'主要思路:
'1、首先进行排序'
'2、用A1=A2判断进区别是否重复,为真则表示重复
'3、对为真的行进行循环删除
'代码如下:
Sub 删除重复二()
Dim LRow As Long
Dim I As Integer
Dim J As Integer
Dim myBoolean As Boolean
Application.ScreenUpdating = False
LRow = Sheets("sheet1").[A65536].End(xlUp).Row
'进行排序
Range("A2:A" & LRow).Sort Key1:=Range("A2")
For I = 2 To LRow
LRow = Sheets("sheet1").[A65536].End(xlUp).Row
For J = 2 To LRow
'得到布尔值
myBoolean = Sheet1.Range("A" & I - 1) = Sheet1.Range("A" & I)
'值为真则进行删除
If myBoolean = True Then
Sheets("sheet1").Cells(I, 1).Delete
End If
Next J
Next I
Application.ScreenUpdating = True
End Sub
|站长邮箱|小黑屋|手机版|Office中国/Access中国 ( 粤ICP备10043721号-1 )
GMT+8, 2024-5-29 04:28 , Processed in 0.055493 second(s), 14 queries .
Powered by Discuz! X3.3
© 2001-2017 Comsenz Inc.