|
发表于 2016-5-8 12:37
|
显示全部楼层
本楼为最佳答案
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Count = 1 Then
'如果不连续
If isContinuity(Target) = False Then
'如果重复了
If isRepeated(Target) = True Then
'清除当前值
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End If
End If
End Sub
'条件1:是否连续
Function isContinuity(Target As Range) As Boolean
Dim area As Range
Dim f As Long
f = firstRow(Target)
'从该值第1次出现的行号,到当前行
Set area = Range(Cells(f, Target.Column), Target)
isContinuity = Application.CountIf(area, Target) = Target.Row - f + 1
End Function
'条件2:是否重复
Function isRepeated(Target As Range) As Boolean
Dim area As Range
'如果当前是第1行,则不重复
If Target.Row = 1 Then isRepeated = False: Exit Function
'从该值第1次出现的行号,到当前行
Set area = Range(Cells(firstRow(Target), Target.Column), Target)
isRepeated = Application.CountIf(area, Target) > 1
End Function
'获取该值的第1次出现的行号
Function firstRow(Target As Range) As Long
Dim rng As Range
Set rng = Columns(Target.Column).Find(Target)
If rng Is Nothing Then firstRow = Target.Row Else firstRow = rng.Row
End Function
help3.rar
(87.21 KB, 下载次数: 9)
|
|